How to use SQLite3?

[Shadowraix]

I want to use it to store data i'll be frequently accessing. I've tried looking at documentation, code, and the wiki. They all either give different or confusing examples. I've never actually had to code in any database format so sqlite is new to me. If anybody could help me out in understanding how to use it, or give very simplistic examples I would appreciate it.

[xoft]

You'll need at least a basic crash-course in general database design and SQL language, those are available all over the Net. Once you have an idea of how to structure your data tables, and how to query them for information and how to insert information into them, you'll be able to work with the API rather naturally. You basically use just 3 functions from the sqlite API - open(), close() and exec(). Open and close are easy - they just open and close the database files. Exec is where all the important stuff happens - it executes ANY sql code.
Of course, in time you'll want to upgrade to using prepared statements. These are what most plugins nowadays use. Basically with statements you say "I want to execute this sql code several times, each time the same statement but with different data. And give me results one at a time".

I'd recommend having a look at the Gallery plugin's Storage_SQLite file, it implements the entire SQLite storage mechanism for the Gallery plugin:
https://github.com/mc-server/Gallery/blo...SQLite.lua
A good example of working with the database is the RemoveArea() function: https://github.com/mc-server/Gallery/blo...#L835-L871
It inserts data to one table and removes data from two other tables.
The LoadAllPlayerAreas() function, on the other hand, queries the database for data in a basic way: https://github.com/mc-server/Gallery/blo...#L835-L871

[Shadowraix]

You'll need at least a basic crash-course in general database design and SQL language, those are available all over the Net. Once you have an idea of how to structure your data tables, and how to query them for information and how to insert information into them, you'll be able to work with the API rather naturally. You basically use just 3 functions from the sqlite API - open(), close() and exec(). Open and close are easy - they just open and close the database files. Exec is where all the important stuff happens - it executes ANY sql code.
Of course, in time you'll want to upgrade to using prepared statements. These are what most plugins nowadays use. Basically with statements you say "I want to execute this sql code several times, each time the same statement but with different data. And give me results one at a time".

I'd recommend having a look at the Gallery plugin's Storage_SQLite file, it implements the entire SQLite storage mechanism for the Gallery plugin:
https://github.com/mc-server/Gallery/blo...SQLite.lua
A good example of working with the database is the RemoveArea() function: https://github.com/mc-server/Gallery/blo...#L835-L871
It inserts data to one table and removes data from two other tables.
The LoadAllPlayerAreas() function, on the other hand, queries the database for data in a basic way: https://github.com/mc-server/Gallery/blo...#L835-L871

Alright, i'll give things a try.

[Shadowraix]

Alright, so I've started learning how SQLite3 works. I've been doing decent so far. I've got my table created and im already doing other stuff with it. One thing confuses me about the DB:exec() function that the documentation on the Lua SQLite3 page doesn't cover. What is udata? What does it represent as a parameter in the callback function?
Also, the third parameter to the exec() function I have no idea what its for and I assume its related to udata.

EDIT: I'm stupid, haha. udata is the third argument. "UserData" its to pass needed data into the callback I suppose.

[xoft]

See, told you it'd be easy

I'd recommend this program for you: http://sqlitebrowser.org/
It allows you to view and edit sqlite database files. And you can even try out the sql queries there, to see what they return, and whether they work as you'd expect them to work.

[Shadowraix]

See, told you it'd be easy

I'd recommend this program for you: http://sqlitebrowser.org/
It allows you to view and edit sqlite database files. And you can even try out the sql queries there, to see what they return, and whether they work as you'd expect them to work.

Already one step ahead of you, I found it and downloaded it before you suggested this. xP

[LogicParrot]

https://www.khanacademy.org/computing/ho...ome-to-sql

[NathanFlurry]

@NiLSPACE's Login plugin ( https://github.com/NiLSPACE/Login ) has some *really* nice tools for managing a database. I *highly* suggest you check it out, especially if you don't have a solid understanding of SQL. (Be sure to include all the files beginning with "c" to make the library work.)

[xoft]

Well, I for one am not so thrilled about that framework of his, it's basically a ton of wrappers for a single SQL string and if you need to do something more advanced, there's no real way of doing it.

[Aggroblut]

Hey, I tested a little bit around but I dont understand why the following code wont work. The first query is working. The Table is created but the Insert dont work. But why? Can someone please tell me what I did wrong.

PLUGIN = nil
function Initialize(Plugin)
	Plugin:SetName("NewPlugin")
	Plugin:SetVersion(1)

	PLUGIN = Plugin
	
	LOG("Initialised " .. Plugin:GetName() .. " v." .. Plugin:GetVersion())
	
	TestDB = {}
	
	TestDB = sqlite3.open('TestDB.sqlite3')
	
	local sql = "CREATE TABLE IF NOT EXISTS TestTable(ID PRIMARY KEY,One,Two)"
	TestDB:exec(sql)
	local sqlq = "INSERT INTO TestTable(ID,One,Two) VALUES ('25','35','45')"
	TestDB:exec(sqlq)
	
	TestDB:close()
		
	return true
end

function OnDisable()
	LOG(PLUGIN:GetName() .. " is shutting down...")
end

Thanks a lot!
Greetings Aggro