Database API v1.01 - Can be operated with MySQL queries!

• Improve the MySQL interpreter to support more complex queries

--Load the API if not done yet
os.unloadAPI("db")
os.loadAPI("db")

--First of all we need a database in which we want to write, so we check if there is already
--a database called "warehouse" and if not, we create a new one
if (not db.dbExists("warehouse")) then
db.createDatabase("warehouse")
end

--Then we create a table called "items". We also check if it is already existing,
--because we will get an error else
--Currently you have to specify the database you want to modify in almost all
--methods, this will be removed in a later version and replaced through
--db.selectDatabase(), like in PHP/MySQL
if (not db.tableExists("warehouse", "items")) then
db.createTable("warehouse", "items")
end

--Next we want to feed our table with some data.
--The syntax of this very long set commmand is quite simple:
--1st parameter: The database you want to modify (in our case "warehouse")
--2nd parameter: The table you want to modify (in our case "items")
--3rd parameter: The row ID, which will in our case be the Minecraft-ID of the item
--4th parameter: The key (column) which should be modified, in our case for every item the count column and the name column
--5th parameter: The (new) value of the key, in our case for every item the count and the name

--Our first item will be stone
db.set("warehouse", "items", 1, "name", "Stone")
db.set("warehouse", "items", 1, "count", "64")

--The second item will be sand
db.set("warehouse", "items", 12, "name", "Sand")
db.set("warehouse", "items", 12, "count", "12")

--And our last item is going to be diamonds *-*
db.set("warehouse", "items", 264, "name", "Diamond")
db.set("warehouse", "items", 264, "count", "1")

--Also we want to create an index so we know which item ID's we
--currently have stored.
--Since there is no item in minecraft with the ID "0" (except air,
--but in my opionion this is no item), we store the index there
--As index I use an serialized lua-table
index = {1, 12, 264}
db.set("warehouse", "items", 0, "index", textutils.serialize(index))

--All of our data is now written into the database.
--Now it is time to read it and present it to the user
--Let's start. First of all we need to read our index file
--Reading is done by using db.get()
--The syntax is the same as db.set(), except the last parameter
--So for the index file we use this:
index = textutils.unserialize(db.get("warehouse", "items", 0, "index"))

--Now we can read all the other items by using a for-loop, since we have the
--index table.
for i = 1, #index do
print(db.get("warehouse", "items", index[i], "name") .. ": " .. db.get("warehouse", "items", index[i], "count"))
end

--The program will output:
--Stone: 64
--Sand: 12
--Diamond: 1
--Using the index makes the program very flexible and expandable,
--but it is also possible to hardcode the ID's.

LayZee, on 13 April 2014 - 11:49 PM said:

Access restricted to http://computercraftdb.wikispaces.com

function deleteDatabase(dbName)
if (dbExists(dbName)) then
  fs.delete("Databases\\" .. dbName)
else
end

function deleteDatabase(dbName)
if (dbExists(dbName)) then
  fs.delete("Databases\\" .. dbName)
else
		end
end

db.set("DatabaseName", "exampleTable", db.getNextFreeID(), "foo", "bar", "var2", "username", "password")

RoD, on 02 June 2014 - 06:49 PM said:

You are missing a end here:

function deleteDatabase(dbName)
if (dbExists(dbName)) then
  fs.delete("Databases\\" .. dbName)
else
end

Needs to be:

function deleteDatabase(dbName)
if (dbExists(dbName)) then
  fs.delete("Databases\\" .. dbName)
else
		end
end

Edit: Quick question: can we add various values to one key?
Something like:

db.set("DatabaseName", "exampleTable", db.getNextFreeID(), "foo", "bar", "var2", "username", "password")

newcat, on 02 June 2014 - 09:49 PM said:

RoD, on 02 June 2014 - 06:49 PM said:

You are missing a end here:

function deleteDatabase(dbName)
if (dbExists(dbName)) then
  fs.delete("Databases\\" .. dbName)
else
end

Needs to be:

function deleteDatabase(dbName)
if (dbExists(dbName)) then
  fs.delete("Databases\\" .. dbName)
else
		end
end

Edit: Quick question: can we add various values to one key?
Something like:

db.set("DatabaseName", "exampleTable", db.getNextFreeID(), "foo", "bar", "var2", "username", "password")

Thank you for mentioning it, I fixed that.

You can't set multiple values for one key yet (though I maybe implement that in the future), but if you combine the multiple strings into one string, you can set this combined string as value.
If you use blanks as separators you can then use the db.splitString() function to split the string back into the single values. But it isn't a good idea to use blanks as separators ;)/>

RoD, on 02 June 2014 - 09:55 PM said:

newcat, on 02 June 2014 - 09:49 PM said:

RoD, on 02 June 2014 - 06:49 PM said:

You are missing a end here:

 function deleteDatabase(dbName) if (dbExists(dbName)) then fs.delete("Databases\\" .. dbName) else end 

Needs to be:

 function deleteDatabase(dbName) if (dbExists(dbName)) then fs.delete("Databases\\" .. dbName) else end end 

Edit: Quick question: can we add various values to one key? Something like:

 db.set("DatabaseName", "exampleTable", db.getNextFreeID(), "foo", "bar", "var2", "username", "password") 

Thank you for mentioning it, I fixed that. You can't set multiple values for one key yet (though I maybe implement that in the future), but if you combine the multiple strings into one string, you can set this combined string as value. If you use blanks as separators you can then use the db.splitString() function to split the string back into the single values. But it isn't a good idea to use blanks as separators ;)/>/>

You're welcome Ok, i hope you implement this as it will add the really important part of the database. Thanks

RoD, on 03 June 2014 - 10:00 PM said:

i think that you can use … as a argument wich will add infinite param. to the function:
http://www.lua.org/pil/5.2.html
http://stackoverflow...guments-lua-5-1
http://stackoverflow...th-of-arguments

tbl = {"bar", "var2", "username", "password"}
db.set("DatabaseName", "exampleTable", db.getNextFreeID(), "foo", db.convertTableToString(tbl))

tbl = db.convertStringToTable(db.get("DatabaseName", "exampleTable", 1, "foo"))

tbl = {"bar", "var2", "username", "password"}
qry = "UPDATE `exampleTable` SET `foo` = '" .. db.convertTableToString(tbl) .. "' WHERE `id` = '" .. db.getNextFreeID() .. "'"
db.query("DatabaseName", qry)

newcat, on 03 June 2014 - 10:46 PM said:

-snip-

KingofGamesYami, on 04 June 2014 - 02:07 PM said:

newcat, on 03 June 2014 - 10:46 PM said:

-snip-

So, you are adding textutils.serialize and textutils.unserialize?

TheGreatSynan, on 02 July 2014 - 10:16 PM said:

Wow. Once you get the external tool going, you can be certain that I will be using this. Good luck!

--This function update / create index of a table:
function index(db, tableName)
		if (dbExists(db)) then
				if (tableExists(db, tableName)) then
						index = {}
						totalEntries = 0  
						path = "Databases\\" .. db .. "\\" .. tableName
						ls = fs.list(path)
						
						for _, table in pairs(ls) do		
						  table = textutils.unserialize(table)						
						  if table ~= 0 then
							totalEntries = totalEntries + 1
							index[totalEntries] = table
						  end					  
						end

						set(db, tableName, 0, "index", textutils.serialize(index))
						return nil
				else
						print("Invalid Table!")
						return nil
				end
		else
				print("Invalid Database!")
				return nil
		end
end

--As index I use an serialized lua-table
index = {1, 12, 264}
db.set("warehouse", "items", 0, "index", textutils.serialize(index))

--As index I use an serialized lua-table
db.index("warehouse", "items")

db.index(db, tableName)

Rokin05, on 03 January 2015 - 06:48 PM said:

—snip—