Lua:SQLite Tutorial: Part 2
From GMod Wiki
Contents |
Part 2: Creating a Player Saving/Loading System
We have to start out making sure that our tables exist, so that we can put data into them.
function Initialize() tables_exist() end
This is going to call our tables_exist() function we made in part 1, on server start up.
Here is where you need to call on that basic Lua knowledge:
function PlayerInitialSpawn( ply ) // Fires after the player spawned for the first time timer.Create("Steam_id_delay", 1, 1, function() // Sets up a little delay cause otherwise the steamid wont be available yet SteamID = ply:SteamID() // Sets the variable SteamID to the players steamID ply:SetNWString("SteamID", SteamID) // Networks the variable so we can use it everywhere timer.Create("SaveStat", 10, 0, function() saveStat( ply ) end) // Creates a timer loop that repeats infinite every 10 secconds to save our stats (Whe get into that later) player_exists( ply ) // Calls our player exists function end) end hook.Add( "PlayerInitialSpawn", "PlayerInitialSpawn", PlayerInitialSpawn ) hook.Add( "Initialize", "Initialize", Initialize ) // Clean code : function PlayerInitialSpawn( ply ) timer.Create("Steam_id_delay", 1, 1, function() SteamID = ply:SteamID() ply:SetNWString("SteamID", SteamID) timer.Create("SaveStat", 10, 0, function() saveStat( ply ) end) player_exists( ply ) end) end
It's commented well enough that you should be able to figure it out if you know Lua well enough. If you have no idea what just happened, you need to scour around the Wiki more and come back.
Next up, we're going to be making two functions: player_exists and new_player and then we fetch the networked(NW) variable SteamID.
function player_exists ( ply ) steamID = ply:GetNWString("SteamID") end function new_player( SteamID, ply ) end
Next, we're going to be using some SQL code to select a player and load his/her stats. However, if the player isn't found, we need to make a data slot for them:
SELECT unique_id, money FROM player_info
This is the basic Select statement. It tells the SQL to select the data for manipulation, in this case, the SQL is being told we're going to manipulate the money of unique_id, from player_info.
Now, let's get this into a query format so we can make Lua talk to SQl.
result = sql.Query("SELECT unique_id, money FROM player_info WHERE unique_id = '"..steamID.."'")
The query above, once again, tells the SQL to manipulate the specified data. The added Where statement, tells it where to manipulate the data. It's kind of the like an if then condition.
Now, let's get this all into our function:
function player_exists( ply ) steamID = ply:GetNWString("SteamID") result = sql.Query("SELECT unique_id, money FROM player_info WHERE unique_id = '"..steamID.."'") if (result) then sql_value_stats( ply ) // We will call this to retrieve the stats sql_value_skills( ply ) // We will call this to retrieve the skills else new_player( steamID, ply ) // Create a new player :D end end
The above code, uses our SQL code from before, and puts it into query format. The SQL will then send the results back to Lua. If the result is true, meaning it was able to find the data we were looking for, then it sets up the players stats and skills. However, if it returns false, meaning the data was not found, it will create a new player. Two birds, one stone.
Wait, we don't have a new_player function... That's the next part.
Creating data in SQLite
If you've made it this far, your ready for data creation.
It's recommended, however, that you read the following article on the insert statement, as you'll be using it frequently:
http://www.w3schools.com/sql/sql_insert.asp
Now then, let us create our player function:
function new_player( SteamID, ply ) steamID = SteamID sql.Query( "INSERT INTO player_info (`unique_id`, `money`)VALUES ('"..steamID.."', '100')" ) result = sql.Query( "SELECT unique_id, money FROM player_info WHERE unique_id = '"..steamID.."'" ) end
What we just did, was setup a query to change the player_info of unique_id's money, to 100. Then ran a query checking the previous.
Next. we need some error handling for this:
function new_player( SteamID, ply ) steamID = SteamID sql.Query( "INSERT INTO player_info (`unique_id`, `money`)VALUES ('"..steamID.."', '100')" ) result = sql.Query( "SELECT unique_id, money FROM player_info WHERE unique_id = '"..steamID.."'" ) if (result) then else Msg("Something went wrong with creating a players info !") end end
This will tell us if we did something wrong.
Next, we just paste the above, work and make some small modifications:
function new_player( SteamID, ply ) steamID = SteamID sql.Query( "INSERT INTO player_info (`unique_id`, `money`)VALUES ('"..steamID.."', '100')" ) result = sql.Query( "SELECT unique_id, money FROM player_info WHERE unique_id = '"..steamID.."'" ) if (result) then sql.Query( "INSERT INTO player_skills (`unique_id`, `speech`, `fish`, `farm`)VALUES ('"..steamID.."', '1', '1', '1')" ) result = sql.Query( "SELECT unique_id, speech, fish, farm FROM player_skills WHERE unique_id = '"..steamID.."'" ) if (result) then Msg("Player account created !\n") sql_value_stats( ply ) sql_value_skills( ply ) else Msg("Something went wrong with creating a players skills !\n") end else Msg("Something went wrong with creating a players info !\n") end end
There, now our players should get setup just fine!
Check Your Work!
If you've followed the tutorial this far, you should have:
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 function new_player( SteamID, ply ) steamID = SteamID sql.Query( "INSERT INTO player_info (`unique_id`, `money`)VALUES ('"..steamID.."', '100')" ) result = sql.Query( "SELECT unique_id, money FROM player_info WHERE unique_id = '"..steamID.."'" ) if (result) then sql.Query( "INSERT INTO player_skills (`unique_id`, `speech`, `fish`, `farm`)VALUES ('"..steamID.."', '1', '1', '1')" ) result = sql.Query( "SELECT unique_id, speech, fish, farm FROM player_skills WHERE unique_id = '"..steamID.."'" ) if (result) then Msg("Player account created !\n") sql_value_stats( ply ) sql_value_skills( ply ) else Msg("Something went wrong with creating a players skills !\n") end else Msg("Something went wrong with creating a players info !\n") end end function player_exists( ply ) steamID = ply:GetNWString("SteamID") result = sql.Query("SELECT unique_id, money FROM player_info WHERE unique_id = '"..steamID.."'") if (result) then sql_value_stats( ply ) // We will call this to retrieve the stats sql_value_skills( ply ) // We will call this to retrieve the skills else new_player( steamID, ply ) // Create a new player :D end end function Initialize() tables_exist() end function PlayerInitialSpawn( ply ) timer.Create("Steam_id_delay", 1, 1, function() SteamID = ply:SteamID() ply:SetNWString("SteamID", SteamID) timer.Create("SaveStat", 10, 0, function() saveStat( ply ) end) player_exists( ply ) end) end hook.Add( "PlayerInitialSpawn", "PlayerInitialSpawn", PlayerInitialSpawn ) hook.Add( "Initialize", "Initialize", Initialize )
You should understand this as well. If you don't, do not continue.
End of Part 2
Well this marks the end of the second part. Make sure you understand everything this far, if you don't, go back to Part 1 or read up on the wiki more. You should be getting pretty good at SQl. If your ready, move on to the third and final part.
