Sqlite native-extension

The Defoldmine
#1

As discussed by @Arônimo on the Defold Discord he was interested in a sqlite native extension. Having built a few lua extensions (from pather packages) a few times I decided to setup a publicly available Defold one here:

The project is now quite usable.

What this project has:

  • A sqlite3 native binding for desktop OS’s - OSX, Windows and Linux
  • Use of the native interface in log.gui_script
  • A gui that has a simple command line interface. Described below.

The command line has:

  • Simple data entry. (supports only backspace).
  • History. Arrow up / down to select previous commands
  • No cursor functionality plain text entry.
  • Output window shows select’s and command errors

The extension is based almost completely on this project here:
https://mroth.net/lua-sqlite3/documentation
Examples and information about its use in lua should work as is. You will always need to add a local sqlite3 = require("libsqlite.sqlite3") to your lua files to use the examples.

Here’s a pic of the functioning gui:

image
image965×673 49.2 KB

The other platforms will be looked at over the coming week.
Hope this helps.

18 Likes
Can I link and use databases in Defold?
Can I link and use databases in Defold?
#2

All usable and working now. Will add android and ios over coming week (all things going well).
Feel free to copy and use you need. Will make it a formal native extension soon too.

7 Likes
#3

Little update. Heres the build repo for the sqlite libs. Trying to org an android and IOS build for it.

7 Likes
#4

this looks like exactly what I need!

I’m trying to get it hooked up in my game, but I’m having trouble figuring out the proper way to actually use it. I can’t make sense of the return value of the db:exec() function. And, looking at your example, you don’t even use the results variable, but do some other stuff instead. I’ll continue poking at it, though.

#5

You can use the examples from the original site:
https://mroth.net/lua-sqlite3/documentation
These should work fine (I’ll check later tonight to make sure).
The exec() method generally is returning errors and trapping exceptions.
If you need to iterate rows you might need to use prepare or the rows iterator like:

-- Returns a row as an integer indexed array
for row in db:irows("SELECT * FROM test") do
  print(row[1], row[2])
end

Theres a number of examples of this in the docs. They should map 1:1. But as I said, I’ll check that.

2 Likes
#6

I had a quick look, and it is using the irows already in this function:

function process_cmd( self )
	local results, errors = self.db:exec( self.cmd )
	if(results) then

		self.log = self.log.."\n[ Sqlite3 ] " .. self.cmd
		if( string.lower(string.sub(self.cmd, 1, 7)) == "select ") then 
			self.log = self.log .. "\n"
			-- Returns a row as an integer indexed array
			for row in self.db:irows(self.cmd) do
				local rowstr = ""
				for idx in pairs(row) do
					pprint(idx, row[idx])
					rowstr = rowstr .. " | ".. tostring(row[idx])
				end
				self.log = self.log .. "\n" .. rowstr .. " | "
			end
			self.log = self.log .. '\n'
		end
		self.log = self.log.."\n[ Sqlite3 ] OK."
	end 
	if(errors) then self.log = self.log.."\n[ Sqlite3 Error ] " .. errors end
	table.insert(self.hist, self.cmd) 
	self.cmd = ""
	self.hist_select = nil
end

This is in the file main/log.gui_script.
The command line at runtime uses this simple parser. You cant really use it for much else without adding more complex command line parsing.

The Defold window is mainly a test window to show how to use it. It is not a production tool. :slight_smile:

#7

Thanks, that was exactly what I was missing! I think I got a handle on it now.

1 Like