Search the Community

FlatDB is an implementation of SQL (or SQL-like) commands for manipulating databases on the hard drive itself. This post covers: Available commands and overview. How to use the API. Limitations. Performance. Future plans. 1. Available commands and overview. The following commands are available within the API: SELECT DELETE UPDATE INSERT WHERE AND OR Tables are human-readable and a single-row table will typically look like this: #$ID|param1|param2 1|value1|value2 Tables are created as files, with their columns (including headers) separated by the "|" character, and their rows separated by "\n". If you want to use any of those characters in the values you want to store in the table, please remember to replace them with another character or string, or they might break the formatting. Here are some syntax and command examples: SELECT * FROM example -- the wildcard (*) will pull all the results from the table. SELECT name|surname FROM example WHERE name = john OR surname = smith -- parameters for selection MUST be separated by "|". SELECT surname|name|age FROM example WHERE age >= 24 -- parameters are returned in the order they were requested. -- With WHERE, AND and OR you can use "=", "!=", ">=", "<=", "<", and ">" DELETE FROM example -- WARNING: calling DELETE without any OR, AND or WHERE statemens will delete the entire table. DELETE FROM example WHERE $ID = 4 -- this will delete only the row with the ID of 4. Note the '$' character. It is used to signify an auto-enumerated field. UPDATE example SET name = jack|surname = jackson WHERE age < 40 -- as with SELECT, the values for SET are separated by "|". INSERT INTO example VALUES (john|smith|32) -- the values need to be wrapped witin brackets "()" and separated by "|". -- if your table has an auto-enumerated field, you don't need to supply that one. However, the rest must be given. 2. How to use the API. First, you need to download it: pastebin get hCyKeJYT flatdb.lua Then, you'll need to call the API: local flatdb = require "flatdb" Since you don't have a database, you have to create one: flatdb.newDatabase("example") -- example being the name of our new database Now, you need to 'connect' to the database, or in other words, tell the API which database you're using: flatdb.connect("example") -- returns true or throws an error if the database doesn't exist. Next, you need to create a new table: flatdb.newTable("table", "$ID|name|surname|age") -- table being the name of the table, and name, surname, and age being the headers we want to insert. -- The "$" before ID signifies an auto-enumerated field. Now, because you're connected to a database, you can start inserting values: flatdb.query("INSERT INTO table VALUES (john|smith|32)") -- there is a neccesary space between VALUES and the open bracket. -- the values we're parsing must be separated by "|". -- currently, it is not possible to pass only the values you want. If you don't have, for example, a surname, -- you can pass "unknown" or something that can be changed later on. Let's say your name is John Doe, not John Smith. And perhaps you're 25, not 32. Maybe you want to change that: flatdb.query("UPDATE table SET age = 25|surname = doe WHERE name = john AND surname = smith") Let's get some data from the table: local result = flatdb.query("SELECT $ID FROM table") -- a statement like this grabs the IDs of every row in the table. -- NOTE: if you're testing from the shell, don't use the 'local' keyword. You might have noticed that a result is returned. Every query returns true upon completion, except SELECT. The SELECT query returns an io.lines iterator, so you will need to loop through it (in our case, we only have one row, so it will loop exactly once): for row in result do print(row) end The io.lines iterator points to a file that contains your output. Depending on what you selected, this file may contain entire rows of the table. How do you get rid of those pesky separators? Let's say we ran this command: local result = flatdb.query("SELECT * FROM table") When we loop through it, we have to split the values into an array to manipulate each column individually. The API provides a splitString function. for row in result do columns = flatdb.splitString(row, "|") -- the splitString function requires a string and a dilemeter (in our case "|"). print(columns[2]) -- because we have a table that's $ID|name|surname|age, this will print only the names end When you're done with a database, you can disconnect by using: flatdb.disconnect() 3. Limitations. The syntax has to be followed somewhat exactly. Spaces in things like "name = john" don't matter, but the space between "VALUES (john|doe)" does matter. When in doubt, double-check the syntax. Everything is done iteratively. The result of SELECT forces you to process the data you returned row by row. Why? Because of the memory limitations of OpenComputers. If a table gets big, there's no way to store it in memory. The first version of FlatDB stored everything in memory as tables (arrays), but that only worked up to a point. This version (a re-write of everything except the logic) is able to work on a computer with 256k memory (Tier 1.5). If you want to break the code, you will be able to. Error checking is implemented, but I can only imagine what possible inputs I've overlooked, and I'm looking forward to fixing that. And it goes without saying (though I'm saying it) - you need the proper amount of space to store this file and the databases / tables you want to work with. Also, I will state again that you can not use "|" nor "\n" in the values you're inputting. If you need that kind of thing, replace them with something else. 4. Performance To test the performance of the library, I set up a computer of the lowest tier that's supported. (Everything is Tier 1, except for memory, which is a single stick of Tier 1.5) I then created a script that will fill a table with random number values between 1 and 100. These are the results of tests: Insert 100 - 21s Read 100 - 1s Read 100 (one discriminatory statement) - 8.5s Read 100 (two discriminatory statements) - 16s Update 100 (one discriminatory statement) - 16s Again, this is on the lowest tier PC. I'm happy with the performance, but I'll improve it where possible. 5. Future plans. Future plans by priority are: Bug fixing Adding error checking Adding new features (I'm looking at you, TOP, MIN and MAX). If you want to submit a bug, please include what the bug is, how to replicate it, and (if possible) the table you're working with. If nothing else, this was a fun exercise.