[Help]SQL

[TheSmart]

Hello bhaiyo!

mujha help chaiya kia koi mujha help karra ga SQL sik na ka liya? mujha porri details sa samja do. pls

[Ryancit2]

Maine tumharay liye abhi abhi fresh SQL db tutorial banaya hai, hope you like it.

Neechay diye gaye kuch steps aap ko SQLite DB setting up or usage ki complete maloomaat daingay aur aap easily SQLite set up kar sakengay.

Step#1: (Setting up)

local dbcon = dbConnect ( "sqlite" , "myDB.db" ) -- Iss se same folder main myDB.db name ki dB file create ho gi. 
local dbcon = dbConnect ( "sqlite" , ":resourceName/myDB.db" ) -- Iss se kisi aur resourceName folder main myDB name ki dB file create ho gi. 
local dbcon = dbConnect ( "sqlite" , ":/myDB.db" ) -- Iss se MTA SA global database folders main myDB.db name ki dB file create ho gi. 

Wesay tou dB file same folder main ho prefer ki jati hai. Iss file ki successful creation ke baad iss puray server-side code main ye database file edit karnay ke lye hamain dbcon variable ki zaroorat ho gi, jo ke file creation ke kaam aya hai.

Step#2: (Making tables)

SQL databases tamaam entries ko tables main save karti hain. Un tables ke under rows aur columns ki entires hoti hai takay hum har entry ko uniquely select kar sakain. dbcon walay variable ke baad ye code dalain jo ke hamara global table ho ga.

local tableQ = dbQuery ( dbcon, "CREATE TABLE IF NOT EXISTS globalTable (colIndex TEXT,colValue TEXT)" ) 

Iss main maine dbQuery ka function use kiya hai jo ke database table ko query karta hai, iss main first argument dbcon hai jo kay hum ne Step#1 main bana hai. dbQuery jahan bhi use ho isay aik dbFile chahiye hoti hai jo ke dbConnect see generate ya read hot hai. Ab hum ne query main aik table banaya hai jis main 2 columns hain, in columns ka name kuch bhi ho sakta hai, maine in ka name colIndex & colValue rakha hai. In column names ke baad maine TEXT likha hai, iss ka matlab hai ke ye column text values hi store karengay, agar koi aur value save karnay ki attempt ki gayi to debug error milega. Iss TEXT ki jaga hum NULL INTEGER REAL STRING BLOB waghera bhi use kar saktay hain aur ya phir isay khali bhi chhor saktay hai, ye optional statement hai.

Step#3: (Calling tables)

SQL tables call karnay ke liye hamain dbConnection (Step#1), tableQuery (Step#2 jaisi lekin wo nahi) aur dbPoll ko use karna ho ga. Kuch aisay:

local localDB = {} -- Ye local table hai server side par, iss main database ki values save hoon gi takay hamain baar baar SQL db call na karna paray. 
local qh = dbQuery(dbcon, "SELECT * FROM globalTable") 
local data = dbPoll(qh, -1) -- Loop the db table named as globalTable. 
if (#data > 0) then -- Kiya db table main kuch data hai? 
    for i, v in pairs(data) do -- Agar haan to is ko loop karain. 
        local ind = v.colIndex -- v.colIndex wo column hai jo hum ne Step#2 main banaya tha. 
        local val = v.colValue -- v.colValue wo column hai jo hum ne Step#2 main banaya tha. 
    if (not localDB[ind]) then 
        localDB[ind] = val -- Local table main db entries save karain. 
    end 
    end 
end 

Yahan maine apna local table is liye banaya hai takay hamain db connection baar baar na call karna paray aur server ki performance sahi rahay. Iss ka aik aur fayeda ye bhi hai ke hum onResourceStart par iss ko dalain to ye resource start hotay hi tamaam db data apnay main save kar le ga aur jab tak ye script chalay gi, hum wohi local table main data save kartay rahain gay aur aakhir main onResourceStop par wo local table ko call kar ke wapis dbTable main save kar sakain gay. Iss ki complete example maine aakhir main likhi hai.

Step#4: (Saving tables)

Ab hum ne db main entries ka table bhi bana liya hai, uss main entries save karnay ke liye hamain dbPoll ka istamaal karna ho ga.

local exec = dbQuery(dbcon, "DROP TABLE IF EXISTS globalTable") -- Hum ne pehlay se bana hua db table remove kar diya hai takay hum apna local table save kar sakain, warna local table aur puranay db table ki entries same ho sakti hain. 
local query = dbQuery(dbcon, "CREATE TABLE IF NOT EXISTS globalTable (colIndex TEXT,colValue TEXT)") -- Dobara table banaya hai. 
for i, v in pairs (localDB) do -- LocalDB aik local table hai jo kay hum ne Step#3 main banaya tha, iss table main hamari entries save hui theen. 
    if (v) then 
        dbExec(dbcon, "INSERT INTO globalTable(colIndex, colValue) VALUES(?, ?)", i, v) -- Ab hum ne wo local table ki entries ko apnay new db table main dal diya hai. 
    end 
end 

Step#5: (Editing/Adding new entries to local table we created)

Hum ne jo local table Step#3 main banaya tha wo hamari db entries save karega, uss main entries save/edit karnay ke liye hum ordinary Lua tables ka format use karengay.

-- Suppose we have a value and a key for it. Value = val and key = key. 
localDB[key] = val -- Iss se localDB table main 'key' ke under value save karega, agar pehlay se hi iss key main value hai to usay edit karega. 
localDB[key] = nil -- Iss se localDB main saved entry ko delete karega. 
  
for k, v in pairs(localDB) do --[[some code]] end -- Iss se hum localDB ko loop kar saktay hain aur CPU usage bhi nahi ho gi, agar hum baar baar dbQuery ko loop karain to CPU usage bohat zyada bhi ho sakti hai. 

Step#6 (Example)

Uper diye gaye tamaam steps ke functions ko maine aik hi example main likha hai takay aap ko sahi samajh a jaye.

local connection = dbConnect("sqlite", "playerKills.db") 
local kills = { 
--["Account"] = kills, 
} 
  
addEventHandler("onResourceStop", resourceRoot, 
    function(resource) 
        if resource ~= getThisResource() then return end 
        local exec = dbQuery(connection, "DROP TABLE IF EXISTS playerKills") 
        local query = dbQuery(connection, "CREATE TABLE IF NOT EXISTS playerKills (account TEXT, kills INTEGER)") 
        for i, v in pairs (kills) do 
            if (v) then 
                dbExec(connection, "INSERT INTO playerKills(account, kills) VALUES(?, ?)", i, v) 
            end 
        end 
    end 
) 
  
addEventHandler("onResourceStart", resourceRoot, 
    function(resource) 
        if resource ~= getThisResource() then return end 
        local data = dbPoll(dbQuery(connection, "SELECT * FROM playerKills"), -1) 
        if (#data > 0) then 
            for i, v in pairs(data) do 
                local acc = v.account 
                local kills = v.kills 
                kills[acc] = kills 
            end 
        end 
    end 
) 
  
function addKill(acc) 
    if (not acc) then return end 
    local oldKills = kills[acc] or 0 
    kills[acc] = oldKills + 1 
    return kills[acc] 
end 
  
-- Abd hum iss function ko call kar saktay hain jab bhi player ki kills increase karni hoon, sirf addKill(account name) likhain aur ye 1 kill uss ki previouse kills main add karega aur sath hi uss ki total kills batayega. 

[TheSmart]

thanks yar bohat helpful hai agar mujha or kuch problem oh gi tuh mai post kardo ga.

[Ryancit2]

Haan main ye forums din main 12+ hours browse karta hoon, to help you guys