daily pastebin goal
68%
SHARE
TWEET

Untitled

a guest Oct 3rd, 2017 55 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. nut.db = nut.db or {}
  2. nut.util.include("nutscript/gamemode/config/sv_database.lua")
  3.  
  4. local function ThrowQueryFault(query, fault)
  5.     MsgC(Color(255, 0, 0), "* "..query.."\n")
  6.     MsgC(Color(255, 0, 0), fault.."\n")
  7. end
  8.  
  9. local function ThrowConnectionFault(fault)
  10.     MsgC(Color(255, 0, 0), "NutScript has failed to connect to the database.\n")
  11.     MsgC(Color(255, 0, 0), fault.."\n")
  12.  
  13.     setNetVar("dbError", fault)
  14. end
  15.  
  16. local modules = {}
  17.  
  18. -- SQLite for local storage.
  19. modules.sqlite = {
  20.     query = function(query, callback)
  21.         local data = sql.Query(query)
  22.         local fault = sql.LastError()
  23.  
  24.         if (data == false) then
  25.             ThrowQueryFault(query, fault)
  26.         end
  27.  
  28.         if (callback) then
  29.             local lastID = tonumber(sql.QueryValue("SELECT last_insert_rowid()"))
  30.  
  31.             callback(data, lastID)
  32.         end
  33.     end,
  34.     escape = function(value)
  35.         return sql.SQLStr(value, true)
  36.     end,
  37.     connect = function(callback)
  38.         if (callback) then
  39.             callback()
  40.         end
  41.     end
  42. }
  43.  
  44. -- tmysql4 module for MySQL storage.
  45. modules.tmysql4 = {
  46.     query = function(query, callback)
  47.         if (nut.db.object) then
  48.             nut.db.object:Query(query, function(data, status, lastID)
  49.                 if (QUERY_SUCCESS and status == QUERY_SUCCESS) then
  50.                     if (callback) then
  51.                         callback(data, lastID)
  52.                     end
  53.                 else
  54.                     if (data and data[1]) then
  55.                         if (data[1].status) then
  56.                             if (callback) then
  57.                                 callback(data[1].data, data[1].lastid)
  58.                             end
  59.  
  60.                             return
  61.                         else
  62.                             lastID = data[1].error
  63.                         end
  64.                     end
  65.  
  66.                     file.Write("nut_queryerror.txt", query)
  67.                     ThrowQueryFault(query, lastID or "")
  68.                 end
  69.             end, 3)
  70.         end
  71.     end,
  72.     escape = function(value)
  73.         if (nut.db.object) then
  74.             return nut.db.object:Escape(value)
  75.         end
  76.  
  77.         return tmysql and tmysql.escape and tmysql.escape(value) or sql.SQLStr(value, true)
  78.     end,
  79.     connect = function(callback)
  80.         if (!pcall(require, "tmysql4")) then
  81.             return setNetVar("dbError", system.IsWindows() and "Server is missing VC++ redistributables!" or "Server is missing binaries for tmysql4!")
  82.         end
  83.  
  84.         local hostname = nut.db.hostname
  85.         local username = nut.db.username
  86.         local password = nut.db.password
  87.         local database = nut.db.database
  88.         local port = nut.db.port
  89.         local object, fault = tmysql.initialize(hostname, username, password, database, port)
  90.  
  91.         if (object) then
  92.             nut.db.object = object
  93.             nut.db.escape = modules.tmysql4.escape
  94.             nut.db.query = modules.tmysql4.query
  95.  
  96.             if (callback) then
  97.                 callback()
  98.             end
  99.         else
  100.             ThrowConnectionFault(fault)
  101.         end
  102.     end
  103. }
  104.  
  105. MYSQLOO_QUEUE = MYSQLOO_QUEUE or {}
  106.  
  107. -- mysqloo for MySQL storage.
  108. modules.mysqloo = {
  109.     query = function(query, callback)
  110.         if (nut.db.object) then
  111.             local object = nut.db.object:query(query)
  112.  
  113.             if (callback) then
  114.                 function object:onSuccess(data)
  115.                     callback(data, self:lastInsert())
  116.                 end
  117.             end
  118.  
  119.             function object:onError(fault)
  120.                 if (nut.db.object:status() == mysqloo.DATABASE_NOT_CONNECTED) then
  121.                     MYSQLOO_QUEUE[#MYSQLOO_QUEUE + 1] = {query, callback}
  122.                     nut.db.connect()
  123.  
  124.                     return
  125.                 end
  126.  
  127.                 ThrowQueryFault(query, fault)
  128.             end
  129.  
  130.             object:start()
  131.         end
  132.     end,
  133.     escape = function(value)
  134.         local object = nut.db.object
  135.  
  136.         if (object) then
  137.             return object:escape(value)
  138.         else
  139.             return sql.SQLStr(value, true)
  140.         end
  141.     end,
  142.     connect = function(callback)
  143.         if (!pcall(require, "mysqloo")) then
  144.             return setNetVar("dbError", system.IsWindows() and "Server is missing VC++ redistributables!" or "Server is missing binaries for mysqloo!")
  145.         end
  146.  
  147.         local hostname = nut.db.hostname
  148.         local username = nut.db.username
  149.         local password = nut.db.password
  150.         local database = nut.db.database
  151.         local port = nut.db.port
  152.         local object = mysqloo.connect(hostname, username, password, database, port)
  153.  
  154.         function object:onConnected()
  155.             nut.db.object = self
  156.             nut.db.escape = modules.mysqloo.escape
  157.             nut.db.query = modules.mysqloo.query
  158.  
  159.             for k, v in ipairs(MYSQLOO_QUEUE) do
  160.                 nut.db.query(v[1], v[2])
  161.             end
  162.  
  163.             MYSQLOO_QUEUE = {}
  164.  
  165.             if (callback) then
  166.                 callback()
  167.             end
  168.         end
  169.  
  170.         function object:onConnectionFailed(fault)
  171.             ThrowConnectionFault(fault)
  172.         end
  173.  
  174.         object:connect()
  175.  
  176.         timer.Create("nutMySQLWakeUp", 300, 0, function()
  177.             nut.db.query("SELECT 1 + 1")
  178.         end)
  179.     end
  180. }
  181.  
  182. -- Add default values here.
  183. nut.db.escape = modules.sqlite.escape
  184. nut.db.query = modules.sqlite.query
  185.  
  186. function nut.db.connect(callback)
  187.     local dbModule = modules[nut.db.module]
  188.  
  189.     if (dbModule) then
  190.         if (!nut.db.object) then
  191.             dbModule.connect(callback)
  192.         end
  193.  
  194.         nut.db.escape = dbModule.escape
  195.         nut.db.query = dbModule.query
  196.     else
  197.         ErrorNoHalt("[NutScript] '"..(nut.db.module or "nil").."' is not a valid data storage method!\n")
  198.     end
  199. end
  200.  
  201. local MYSQL_CREATE_TABLES = [[
  202. CREATE TABLE IF NOT EXISTS `nut_characters` (
  203.     `_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  204.     `_name` varchar(70) NOT NULL,
  205.     `_desc` text NOT NULL,
  206.     `_model` varchar(160) NOT NULL,
  207.     `_attribs` varchar(500) DEFAULT NULL,
  208.     `_schema` varchar(24) NOT NULL,
  209.     `_createTime` int(11) unsigned NOT NULL,
  210.     `_lastJoinTime` int(4) DEFAULT NULL,
  211.     `_steamID` bigint(20) unsigned NOT NULL,
  212.     `_data` longtext,
  213.     `_money` int(11) unsigned DEFAULT NULL,
  214.     `_faction` varchar(50) NOT NULL,
  215.     PRIMARY KEY (`_id`)
  216. );
  217.  
  218. CREATE TABLE IF NOT EXISTS `nut_inventories` (
  219.     `_invID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  220.     `_charID` int(11) unsigned NOT NULL,
  221.     `_invType` varchar(24) DEFAULT NULL,
  222.     PRIMARY KEY (`_invID`)
  223. );
  224.  
  225. CREATE TABLE IF NOT EXISTS `nut_items` (
  226.     `_itemID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  227.     `_invID` int(11) unsigned NOT NULL,
  228.     `_uniqueID` varchar(60) NOT NULL,
  229.     `_data` varchar(500) DEFAULT NULL,
  230.     `_x` smallint(4) NOT NULL,
  231.     `_y` smallint(4) NOT NULL,
  232.     PRIMARY KEY (`_itemID`)
  233. );
  234.  
  235. CREATE TABLE IF NOT EXISTS `nut_players` (
  236.     `_steamID` bigint(20) NOT NULL,
  237.     `_steamName` varchar(32) NOT NULL,
  238.     `_playTime` int(11) unsigned DEFAULT NULL,
  239.     `_address` varchar(15) DEFAULT NULL,
  240.     `_lastJoin` int(11) unsigned DEFAULT NULL,
  241.     `_data` text,
  242.     PRIMARY KEY (`_steamID`)
  243. );
  244. ]]
  245.  
  246. local SQLITE_CREATE_TABLES = [[
  247. CREATE TABLE IF NOT EXISTS `nut_characters` (
  248.     `_id` INTEGER PRIMARY KEY,
  249.     `_name` TEXT,
  250.     `_desc` TEXT,
  251.     `_model` TEXT,
  252.     `_attribs` TEXT,
  253.     `_schema` TEXT,
  254.     `_createTime` INTEGER,
  255.     `_lastJoinTime` INTEGER,
  256.     `_steamID` INTEGER,
  257.     `_data` TEXT,
  258.     `_money` INTEGER,
  259.     `_faction` TEXT,
  260.     `_background` TEXT
  261. );
  262.  
  263. CREATE TABLE IF NOT EXISTS `nut_inventories` (
  264.     `_invID` INTEGER PRIMARY KEY,
  265.     `_charID` INTEGER,
  266.     `_invType` TEXT
  267. );
  268.  
  269. CREATE TABLE IF NOT EXISTS `nut_items` (
  270.     `_itemID` INTEGER PRIMARY KEY,
  271.     `_invID` INTEGER,
  272.     `_x` INTEGER,
  273.     `_y` INTEGER,
  274.     `_uniqueID` TEXT,
  275.     `_data` TEXT
  276. );
  277.  
  278. CREATE TABLE IF NOT EXISTS `nut_players` (
  279.     `_steamID` INTEGER,
  280.     `_steamName` TEXT,
  281.     `_playTime` INTEGER,
  282.     `_address` TEXT,
  283.     `_lastJoin` INTEGER,
  284.     `_data` TEXT
  285. );
  286. ]]
  287.  
  288. local DROP_QUERY = [[
  289. DROP TABLE IF EXISTS `nut_characters`;
  290. DROP TABLE IF EXISTS `nut_items`;
  291. DROP TABLE IF EXISTS `nut_players`;
  292. DROP TABLE IF EXISTS `nut_inventories`;
  293. ]]
  294.  
  295. function nut.db.wipeTables()
  296.     local function callback()
  297.         MsgC(Color(255, 0, 0), "[Nutscript] ALL NUTSCRIPT DATA HAS BEEN WIPED\n")
  298.     end
  299.    
  300.     if (nut.db.object) then
  301.         local queries = string.Explode(";", DROP_QUERY)
  302.  
  303.         for i = 1, 4 do
  304.             nut.db.query(queries[i], callback)
  305.         end
  306.     else
  307.         nut.db.query(DROP_QUERY, callback)
  308.     end
  309.  
  310.     nut.db.loadTables()
  311. end
  312.  
  313. local resetCalled = 0
  314. concommand.Add("nut_recreatedb", function(client, cmd, arguments)
  315.     -- this command can be run in RCON or SERVER CONSOLE
  316.     if (!IsValid(client)) then
  317.         if (resetCalled < RealTime()) then
  318.             resetCalled = RealTime() + 3
  319.  
  320.             MsgC(Color(255, 0, 0), "[Nutscript] TO CONFIRM DATABASE RESET, RUN 'nut_recreatedb' AGAIN in 3 SECONDS.\n")
  321.         else
  322.             resetCalled = 0
  323.            
  324.             MsgC(Color(255, 0, 0), "[Nutscript] DATABASE WIPE IN PROGRESS.\n")
  325.            
  326.             hook.Run("OnWipeTables")
  327.             nut.db.wipeTables()
  328.         end
  329.     end
  330. end)
  331.  
  332. function nut.db.loadTables()
  333.     if (nut.db.object) then
  334.         -- This is needed to perform multiple queries since the string is only 1 big query.
  335.         local queries = string.Explode(";", MYSQL_CREATE_TABLES)
  336.  
  337.         for i = 1, 4 do
  338.             nut.db.query(queries[i])
  339.         end
  340.     else
  341.         nut.db.query(SQLITE_CREATE_TABLES)
  342.     end
  343.  
  344.     hook.Run("OnLoadTables")
  345. end
  346.  
  347. function nut.db.convertDataType(value)
  348.     if (type(value) == "string") then
  349.         return "'"..nut.db.escape(value).."'"
  350.     elseif (type(value) == "table") then
  351.         return "'"..nut.db.escape(util.TableToJSON(value)).."'"
  352.     end
  353.  
  354.     return value
  355. end
  356.  
  357. function nut.db.insertTable(value, callback, dbTable)
  358.     local query = "INSERT INTO "..("nut_"..(dbTable or "characters")).." ("
  359.     local keys = {}
  360.     local values = {}
  361.  
  362.     for k, v in pairs(value) do
  363.         keys[#keys + 1] = k
  364.         values[#keys] = k:find("steamID") and v or nut.db.convertDataType(v)
  365.     end
  366.  
  367.     query = query..table.concat(keys, ", ")..") VALUES ("..table.concat(values, ", ")..")"
  368.     nut.db.query(query, callback)
  369. end
  370.  
  371. function nut.db.updateTable(value, callback, dbTable, condition)
  372.     local query = "UPDATE "..("nut_"..(dbTable or "characters")).." SET "
  373.     local changes = {}
  374.  
  375.     for k, v in pairs(value) do
  376.         changes[#changes + 1] = k.." = "..(k:find("steamID") and v or nut.db.convertDataType(v))
  377.     end
  378.  
  379.     query = query..table.concat(changes, ", ")..(condition and " WHERE "..condition or "")
  380.     nut.db.query(query, callback)
  381. end
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top