LUA:SQLite Tutorial

From GMod Wiki

Contents 1 Sql Tutorial 2 What Do We Need? 3 Basic SQL Knowledge 4 Coding

Sql Tutorial

Edit by Xyro(Quincy18 on FP) : The Facepunch thread is a bit more advanced (images etc.) So if you don't understand anything try too look it up there

Also notice that this tutorial lets you learn how to interact with the garrysmod built in databases, not an external database.

This Tutorial was written entirely by quincy18 [1], a Facepunch user. His original thread can be found here.[2]

This tutorial is going to teach you how to make a stats and money system, via MySQL. By the end you should have a fairly decent understanding of the tool.

Save it in you gamemode folder as sql_database.lua, so you may reference it at any time.

Onto the tutorial!

---

What Do We Need?

Knowledge Prior to Using the Tutorial:

- A basic knowledge of SQL.

- Some Lua experience.

Actual Coding Knowledge:

- A database setup function.

- A players setup function.

- A retrieving function.

- A saving function.

All of the above is explained in this tutorial.

Basic SQL Knowledge

First and foremost: This assumes you have an IQ above 10, and will not explain to you advanced topics.

SQL has different parts, a database, tables and rows.

A database can be called a main file to store your tables and a table is where you store you're different variables.

Look at this basic schematic to get a better idea of what is being said: [3]

It is recommended, that you read the following pages, as they will explain most of the functions used in this tutorial:

http://www.w3schools.com/sql/sql_syntax.asp

http://www.w3schools.com/sql/sql_select.asp

http://www.w3schools.com/sql/sql_where.asp

http://www.w3schools.com/sql/sql_insert.asp

http://www.w3schools.com/sql/sql_update.asp

After reading, the above, let's continue on.

---

Coding

Fortunately for us, GMod will create the database for us.

So lets get started.

We begin by creating a function called tables_exist and adding an if statement to see if the table we want to make exists.

 
function tables_exist()
	if sql.TableExists("player_info") && sql.TableExists("player_skills") then //sql.TableExists does exactly what it says
		Msg("Both tables already exist !")
	else
		// Create the tables
	end
end
 

Next we need to check if either one of the tables doesn't exist and if they don't create them:

 
function tables_exist()
	if (sql.TableExists("player_info") && sql.TableExists("player_skills")) then
		Msg("Both tables already exist !")
	else
		if (!sql.TableExists("player_info")) then // ! = not
			// Create the table here
		end
		if (!sql.TableExists("player_skills")) then
			// Create the table here
		end
	end
end
 

Now we need to create our table:

 
CREATE TABLE player_info
 

Next, we have to add the rows to our table:

 
query = "CREATE TABLE player_info ( unique_id varchar(255), money int )"
 

Here I added a variable to hold our query (What we instruct the database to do) and added a unique id for the steam id so we can sort somebody out of our database (varchar mean that it can be a number or a letter) we can do the same for the other table :

 
query = "CREATE TABLE player_skills ( unique_id varchar(255), speech int, fish int, farm int )"
 

Now that all of that is done, let's put it altogether:

 
function tables_exist()
	if (sql.TableExists("player_info") && sql.TableExists("player_skills")) then
		Msg("Both tables already exist !")
	else
		if (!sql.TableExists("player_info")) then
			query = "CREATE TABLE player_info ( unique_id varchar(255), money int )"
			result = sql.Query(query)
		end
		if (!sql.TableExists("player_skills")) then
			query = "CREATE TABLE player_skills ( unique_id varchar(255), speech int, fish int, farm int )"
			result = sql.Query(query)
		end
	end
end
 

There, now it's all in one piece. Notice, the sql.Query() function. It allows us to send queries (instructions) to the database. It is necessary to use, so that our database receives the code, and can check for errors, if any.

Next, let's make sure everything worked well:

 
function tables_exist()
 
	if (sql.TableExists("player_info") && sql.TableExists("player_skills")) then
		Msg("Both tables already exist !")
	else
		if (!sql.TableExists("player_info")) then
			query = "CREATE TABLE player_info ( unique_id varchar(255), money int )"
			result = sql.Query(query)
			if (sql.TableExists("player_info")) then
				Msg("Succes ! table 1 created \n")
			else
				Msg("Somthing went wrong with the player_info query ! \n")
				Msg( sql.LastError( result ) .. "\n" )
			end	
		end
		if (!sql.TableExists("player_skills")) then
			query = "CREATE TABLE player_skills ( unique_id varchar(255), speech int, fish int, farm int )"
			result = sql.Query(query)
			if (sql.TableExists("player_skills")) then
				Msg("Succes ! table 2 created \n")
			else
				Msg("Somthing went wrong with the player_skills query ! \n")
				Msg( sql.LastError( result ) .. "\n" )
			end	
		end
	end
 
end
 

The above code, shows us how we can do MySQL error handling!

---

If your comfortable with the above information, move on to Part 2 of the tutorial!