· 7 years ago · Oct 17, 2018, 09:42 PM
1local CREATE_TABLES = [[
2CREATE TABLE IF NOT EXISTS jericho_players (
3 steamid64 text PRIMARY KEY NOT NULL,
4 steam_name varchar(32) NOT NULL,
5 play_time float(5) DEFAULT 0,
6 player_char_ids text DEFAULT "{}",
7 player_data longtext DEFAULT "{}"
8);
9
10CREATE TABLE IF NOT EXISTS jericho_characters (
11 id INTEGER unsigned PRIMARY KEY NOT NULL,
12 name varchar(70) NOT NULL,
13 description text NOT NULL,
14 model_data longtext NOT NULL,
15 create_time int unsigned NOT NULL,
16 last_join_time int DEFAULT NULL,
17 steamid64 text NOT NULL,
18 faction varchar(50) NOT NULL,
19 money float(5) DEFAULT NULL,
20 health float(5) NOT NULL DEFAULT 100,
21 max_health float(5) NOT NULL DEFAULT 100,
22 armor float(5) NOT NULL DEFAULT 0,
23 max_armor float(5) NOT NULL DEFAULT 100,
24 hunger float(24) NOT NULL DEFAULT 100,
25 thirst float(24) NOT NULL DEFAULT 100,
26 stamina float(24) NOT NULL DEFAULT 100,
27 metadata longtext DEFAULT "{}"
28);
29]]
30
31local DROP_ALL_QUERY = [[
32DROP TABLE IF EXISTS jericho_players;
33DROP TABLE IF EXISTS jericho_characters;
34DROP TABLE IF EXISTS jericho_inventories;
35]]
36
37--- \brief Initializes the database and creates tables if they don't exist.
38function jericho.db.init()
39 local data = sql.Query(CREATE_TABLES)
40 local error = sql.LastError()
41
42 if (data == false) then -- Error reporting, especially as we try to get the initial SQL queries setup.
43 jericho.error("jericho.db.init(): " .. error .. "\n\t* for query:\n" .. CREATE_TABLES)
44 end
45end
46
47--- \brief This makes entered strings safe from SQL injection.
48--- \p str The string to clean sql injections from.
49function jericho.db.clean(str)
50 jericho.assert(type(str) == "string", "jericho.db.escape(str): str is not a string.")
51 return sql.SQLStr(str, true)
52end
53
54--- \brief Queries the database and executes a callback given with the data
55--- queried.
56--- \p query A string with a valid sql query.
57--- \p type (OPTIONAL) The type of query, either "row" or "value"
58--- \p rownum (OPTIONAL) If using type "row", the row number of the result we want to return (see sql.QueryRow)
59function jericho.db.query(query, query_type, rownum)
60 jericho.assert(type(query) == "string", "jericho.db.query(str): query is " .. type(query) .. " type not a string type.")
61 local data
62
63 if query_type and query_type == "value" then -- sql.QueryValue for the first value
64 data = sql.QueryValue(query)
65 elseif query_type and query_type == "row" then -- sql.QueryRow to get the specified row only.
66 if rownum then
67 data = sql.QueryRow( query, rownum )
68 else
69 jericho.error("jericho.db.query was called with query_type 'row', but no rownum was supplied. Aborting!")
70 end
71 else -- sql.Query to get all values.
72 data = sql.Query(query)
73 end
74
75 jericho.print("QUERY RAN: " .. query)
76
77 local error = sql.LastError()
78
79 if (data == false) then
80 jericho.error("jericho.db.query(query, callback): " .. error .. "\n\t* for query:\n" .. query)
81 end
82
83 return data, tonumber(sql.QueryValue("SELECT last_insert_rowid()"))
84end
85
86--- \brief Converts a given value into a safe SQLStr for use in queries if value type is a string or table.
87--- \p value A value to clean, will escape a string or table OR just return the value if not of said types.
88function jericho.db.clean_data(value)
89 if type(value) == "string" then
90 return "'" .. jericho.db.clean(value) .. "'"
91 elseif type(value) == "table" then
92 return "'" .. jericho.db.clean(util.TableToJSON(value)) .. "'"
93 else
94 return value
95 end
96end
97
98--- \brief Inserts a table of data into the relevant table.
99--- \p tbl A table of data to be inserted into the specified database table.
100--- \p dbTabl (OPTIONAL) The name of the database table to insert the data into. Defaults to characters.
101function jericho.db.insert_table(tbl, dbTable)
102 jericho.assert( type(tbl) == "table", "jericho.db.insert_table(tbl, dbTable): tbl is a " .. type(tbl) .. ", expected a table." )
103 jericho.assert( type(dbTable) == "string", "jericho.db.insert_table(tbl, dbTable): dbTable is a " .. type(dbTable) .. ", expected a string." )
104
105 local query = "INSERT INTO " .. ( "jericho_" .. (dbTable or "characters")) .. " (" -- the non-string brackets are so we concat the values together in the "right" order. Not sure if required.
106
107 local keys = {}
108 local values = {}
109
110 for k, v in pairs(tbl) do
111 keys[ #keys + 1 ] = k
112 values[ #keys ] = jericho.db.clean_data(v)
113 end
114
115 query = query .. table.concat(keys, ", ") .. ") VALUES (" .. table.concat(values, ", ") .. ");"
116
117 return jericho.db.query(query) -- returning so we can error check if wanted.
118end
119
120--- \brief Updates a value in the given database table.
121--- WARNING: The condition value is NOT cleaned here! PLEASE remember to jericho.db.clean() if you are getting values from the client (for some inexplicable reason.)
122--- \p tbl A table of values which we want to update.
123--- \p dbTable (OPTIONAL) The name of the database table to update the data. Defaults to characters.
124--- \p condition (OPTIONAL) The condition that the row must have (UPDATE ... WHERE condition)
125function jericho.db.update_table(tbl, dbTable, condition)
126 jericho.assert( type(tbl) == "table", "jericho.db.update_table(tbl, dbTable, condition): tbl is a " .. type(tbl) .. ", expected a table." )
127 jericho.assert( type(dbTable) == "string", "jericho.db.update_table(tbl, dbTable, condition): tbTable is a " .. type(dbTable) .. ", expected a string." )
128
129 local query = "UPDATE " .. ( "jericho_" .. (dbTable or "characters")) .. " SET "
130 local changes = {}
131
132 for k, v in pairs(tbl) do
133 changes[ #changes + 1 ] = k .. "=" .. jericho.db.clean_data(v)
134 end
135
136 query = query .. table.concat(changes, ", ") .. ( condition and ( " WHERE " .. condition ) or "" )
137 return jericho.db.query(query)
138end
139
140--[[
141 Below are some more "utility" based functions. They are common SQL queries that we will be using.
142]]--
143
144--- \brief Checks if a player has an entry in the jericho_players table, and if so returns their value.
145--- \p steamid The SteamID64 of the player in question.
146function jericho.db.get_player_info( steamid )
147 jericho.assert( type(steamid) == "string", "jericho.db.get_player_info(steamid): steamid is a " .. type(steamid) .. ", expected a string." )
148 return jericho.db.query("SELECT * FROM jericho_players WHERE steamid64=" .. steamid .. ";", "value")
149end
150
151--- \brief Gets all character information from a given id (if exists) and returns their table of data.
152--- \p id The ID of the character we want to obtain info from.
153function jericho.db.get_character_info(id)
154 jericho.assert( type(id) == "number", "jericho.db.get_character_info(id): id is type " .. type(id) .. ", expected an int.")
155 return jericho.db.query("SELECT * FROM jericho_characters WHERE id=" .. id .. ";", "value")
156end
157
158
159--[[
160 THE FOLLOWING ARE DEBUG COMMANDS AND SHOULD PROBABLY BE DELETED AFTERWARDS.
161]]--
162
163--- \brief A utility command to completely remove all database info.
164--- NOTE: Must be removed before the gamemode goes live.
165function jericho.db.drop_all_tables()
166 jericho.db.query(DROP_ALL_QUERY)
167 jericho.db.init()
168end
169
170concommand.Add("jericho_drop_all_tables", function(client, cmd, arguments)
171 if not IsValid(client) then
172 jericho.db.drop_all_tables()
173 end
174end)
175
176--- \brief A utility command to remove the player database information of the person who calls the command.
177--- TODO: This really, really, REALLY, needs to be removed before the server goes live.
178--- \p ply The player that calls the command.
179concommand.Add("jericho_debug_delete_player_info", function(ply, cmd, arguments)
180 local steamid64 = ply:SteamID64()
181 jericho.db.query("DELETE FROM jericho_players WHERE steamid64=" .. steamid64 .. ";" )
182
183 -- Call the PlayerInitialSpawn hook, because that's what we *probably* are testing.
184 gamemode.Call("PlayerInitialSpawn", ply)
185
186end)