This is a read-only snapshot of the ComputerCraft forums, taken in April 2020.
fyndor's profile picture

Is my database engine design a good idea

Started by fyndor, 07 February 2013 - 06:42 PM
fyndor #1
Posted 07 February 2013 - 07:42 PM
I am trying to design a database engine to be used in ComputerCraft. As far as I can tell, there is no file seeking capabilities in CC so to read data at a certain position in a file I would need to first read all the data before that position which would have performance penalty. I believe I have come up with a novel solution to this, but I don't know enough about NTFS (I'm on windows) to know whether this would be better or worse.

In my design, a table would be represented as a folder. Each record would be represented as a file inside the table folder. The record files' names would be the record ID (1, 2, 3, etc). I would persist indexes as one file per index, but I would keep them in memory for speed reasons. Changes to an index would be written to a log which could be used to update the index to a valid state in case of a crash, but during normal operation I would save the in-memory version of the index to disk at a chosen interval while clearing the log.

I chose this design because I can use the fs api to go directly to the record without having to do a bunch for read calls on a file to find the record. I have no idea of how the directory tree is stored in NTFS or other file systems, so this may not give me the performance gains I hope for. On it's surface though it seems like it could work out well. At the very least I can safely assume that it would cost more disk space than a single file table/db design. I would never design a database engine like this outside of ComputerCraft, but with the file IO restrictions that are in place this seems like a decent idea. What do you guys think?
Lyqyd #2
Posted 07 February 2013 - 08:01 PM
Split into new topic.
LBPHacker #3
Posted 07 February 2013 - 10:32 PM
Databases… Good idea so far… But… I think creating a table, adding some values, and then serializing it and saving it into a text file is much easier.
theoriginalbit #4
Posted 07 February 2013 - 10:39 PM
Databases… Good idea so far… But… I think creating a table, adding some values, and then serializing it and saving it into a text file is much easier.
I agree. And if your concerned about security then you could serialize, encrypt, save. then read, decrypt, unserialize.
corisco1917 #5
Posted 08 February 2013 - 12:38 AM
good, i have some functions already in that direction!!!

it's funny how i had thought the almost the same aproach as yours on this projects… but in mine, each"table"(working as collections as in mongodb) are folders name and inside the folder i have archive containing lua tables….

look at my db API, i hope it can help u build yours:

db_setup program


Spoiler



local startup_function = [[ for _, side in pairs(redstone.getSides()) do
				rednet.open(side)
end]]
if not fs.exists("disk") then
print("Please Insert database Disk")
else
if not fs.exists("disk/db") then
local server = {}
for _, side in pairs(redstone.getSides()) do
			   rednet.open(side)
end
server.id = os.getComputerID()
server.x, server.y, server.z = gps.locate(5)
fs.makeDir("disk/db")
fs.makeDir("disk/db/turtle")
fs.makeDir("disk/db/chest")
fs.makeDir("disk/db/computer")
local file = fs.open("disk/db/server", "w")
server = textutils.serialize(server)
file.write(server)
file.close()
local startup = fs.open("startup", "w")
startup.write(startup_function)
startup.close()
else
print("database already installed")
end
end


db API file

Spoiler



if fs.exists("disk/db") then
print("db api loaded")
else
return false
end
local object_type
local Collection = {}
Collection.__index = Collection
local collections = { turtle = "disk/db/turtle", computer = "disk/db/computer", chest = "disk/db/chest", } --file names in database and its path
local fenv = getfenv() --get global env for this
if turtle then
object_type = "turtle"
else
object_type = "computer"
end

local function checkInput(self, table)
if self == nil then
print("you must use this command like:")
print("db.collection_name:method_name(input)")
return false
elseif type(table) ~= "table" then
print("db::the input is not a table")
return false
end
return true
end

function Collection.create(path, type)
local collection = {}
setmetatable(collection, Collection)
collection.path = path
collection.type = type
return collection
end

function Collection:save(table)
if not checkInput(self, table) then
return false
end
if fs.exists(self.path.."/"..self.type.."_"..table.id) then
print("there is already a table for this, pelase use update function")
end
  local nTable = textutils.serialize(table)
  local file = fs.open(self.path.."/"..self.type.."_"..table.id, "w")
  file.write(nTable)
  file.close()
end

function Collection:update(table)
if not checkInput(self, table) then
return false
end
if not fs.exists(self.path.."/"..self.type.."_"..table.id) then
print("there isnt a table for this, pelase use save function")
return false
end
local reading_file = fs.open(self.path.."/"..self.type.."_"..table.id, "r")
local oTable = textutils.unserialize(read_file.readAll())
reading_file.close()
local nTable = oTable
for key,value in pairs(table) do
if type(table[key]) == "table" then
for k, v in pairs(value) do
if type(nTable[key]) ~= "table" then
   nTable[key] = {}
end
nTable[key][k] = v
end
else
nTable[key] = value
end
end
local nTable = textutils.serialize(nTable)
local opened_file = fs.open(self.path.."/"..self.type.."_"..table.id, "w")
opened_file.write(nTable)
  opened_file.close()
end

function Collection:delete(id)
if not checkInput(self, false) then
return false
end
if not fs.exists(self.path.."/"..self.type.."_"..table.id) then
print("the file you're trying to delete doesn\'t exists ")
return false
end
fs.delete(self.path.."/"..self.type.."_"..id)
end

for key,value in pairs(collections) do --creates global objects for collection(file names in databalse)
	if type(fenv[key]) ~= "table" then
		fenv[key] = {}
	end
	fenv[key] = Collection.create(value, key)
end


i have models for the database to be implemented, this models would represent collections (not tables, since mine will be no sql)… in the models you would specify the fields required for that object, wich fields are required and not required and the type of data these field will receive….

then on loadup of API i will create objects to be called by the api to work with the models and generate lua tables as specified on the models….so for example if you create a model named turtle the api on load will create a global "object" named turtle with the fields required as input and data type of fields.

but these is not implemented yet…

today i have predefined collections with only the archive path specified for creating global objects that will represent my collections of files:
local collections = { turtle = "disk/db/turtle", computer = "disk/db/computer", chest = "disk/db/chest", }

my functions for saving a new lua-table in a file (save method), updating (update method) and delete method are working pretty well…

to use the db u must do like: db.collection:action — or ex: db.turtle:save(new_table_to_be_stored), these method will create a file named turtle_id with whaterver info you pass as a paramether to the method.

my db api is still very raw and not finished, but i hope it can give you some ideias for yours… im trying to follow up as much as lua let me the real nosql database called mongodb:
http://horicky.blogs...chitecture.html
PixelToast #6
Posted 08 February 2013 - 03:06 AM
o.o reminds me of my API that seamlessly saves a table
http://pixeltoast.x64.me/cc/datahost
fyndor #7
Posted 08 February 2013 - 08:57 AM
Databases… Good idea so far… But… I think creating a table, adding some values, and then serializing it and saving it into a text file is much easier.

Because LUA and databases both use the word table for different things I may be confused, but I do intend to store records as LUA tables. I didn't mention that in my post because it wasn't relevant to the question I had. It is certainly relevant though if when talking about this design in its entirety. Technically I could store the records in a number of different formats and achieve similar results, but I was in fact going to store records as serialized LUA tables. What I am not going to do is store DB tables as LUA tables. The reason is that if any record changes in the table, you would then need to persist the entire LUA table back to disk. With my design, if a record changes, I only have to update that one record. This is important in terms of performance. Also, due to the restriction on file size, my database may not fit on one CC disk. I won't do this at the start, but if I find the need to implement it, I intend on allowing the database to span multiple disks / computers. For instance, say I have a database partitioned across 3 disks. I could split the records up according to record ID. So disk one could store record 1, 4, and 7, disk two could store 2, 5, and 8, and disk three could store 3, 6, and 9. So if a user asks for record 5, I know that it will be on disk two without having to check first because I can write a formula to determine which disk it will be on. This could not be achieved if my DB tables were themselves LUA tables..
fyndor #8
Posted 08 February 2013 - 09:06 AM
good, i have some functions already in that direction!!!

it's funny how i had thought the almost the same aproach as yours on this projects… but in mine, each"table"(working as collections as in mongodb) are folders name and inside the folder i have archive containing lua tables….

look at my db API, i hope it can help u build yours:

Thanks for the post. I will check it out more in depth after work. I would read it now, but unfortunately I am getting paid to code manufacturing software not Minecraft CC software. :(/> Since I made the reply above to LPBHacker I didn't want you to think I ignored your post since you did put a bit of effort in to it.