SQL Checking

[.:HyPeX:.]

Hey guys, would it lag to do a overrun check on a database every sec (This should go over maybe 30000 serials if a crowded server, or more) to check if they're muted and their mute has expired, or is there any way i could make this easier?

EDIT: Thought of going just over the connected players (As it will be serial-based tables in the database), but still idk. (Will it lag with big quantity of players? like 150)

EDIT2: If a table exists and i try to create it again, will it be overwritten?

[xXMADEXx]

Rather than scanning the database evertime a player joins or something, I would make a table that manages everything, and then updated the database when the resource is stopped. When the resource is started, then put all of the data into the table.

[.:HyPeX:.]

Hmmm good idea.

I got a question into SQL, I'm trying to setup a table for each player in the server, how can i limit the table to only 1 record and select to update only that one?

I've done this so far:

If i want to insert down the data, i dont know how to check if it is the first time. (As to limit it to only one data)

  
function MutePlayer(player,time,responsible,reason) 
local serial = getPlayerSerial(player) 
local startTime = getRealTime().timestamp 
local endTime = getRealTime().timestamp + time 
local Duration = endTime - startTime 
local PlayerName = string.gsub(getPlayerName(player), "#%x%x%x%x%x%x", "") 
local query = dbExec(Database, "CREATE TABLE IF NOT EXISTS "..serial.." (StartTime int, EndTime int, Duration int, PlayerName varchar, ResponsibleName varchar, Reason varchar );") 
local dataExec = dbExec(Databse, "UPDATE "..serial.." SET StartTime='"..startTime.."', EndTime='"..endTime.."', Duration='"..Duration.."', PlayerName='"..PlayerName.."', ResponsibleName='"..responsible.."', Reason='"..reason.."';")                        
end 
  

Also, i'd apprieciate an example of how to load this back into a lua table.

[.:HyPeX:.]

Bump, i've created down this function to save down everything in my table to a database on resource stop:

  
function SaveDownOnResourceStop(resource) 
if resource ~= getThisResource() then return end 
    for i=1, #Punished do 
        local exec = dbQuery(Database, "DROP TABLE IF EXISTS"..Punished[i][2]) 
        local result = dbPoll(exec, -1) 
        local query = dbQuery(Database, "CREATE TABLE "..Punished[i][2].." (Serial varchar, StartTime int, EndTime int, Duration int, PlayerName varchar, ResponsibleName varchar, Reason varchar );") 
        local result = dbPoll(query, -1) 
        local dataSaving = dbQuery(Database, "INSERT INTO "..Punished[i][2].." (Serial, StartTime, EndTime, Duration, PlayerName, ResponsibleName, Reason) VALUES ("..Punished[i][2]..","..Punished[i][3]..","..Punished[i][4]..","..Punished[i][5]..","..Punished[i][6]..","..Punished[i][7]..","..Punished[i][8]..")") 
        local result = dbPoll(query, -1) 
    end 
end 
addEventHandler("onResourceStop", resourceRoot, SaveDownOnResourceStop) 

EDIT: How can i return all values from all tables with select? i cant find a way to run over all existing tables..

Like: SELECT * FROM *

[tosfera]

Bump, i've created down this function to save down everything in my table to a database on resource stop:

  
function SaveDownOnResourceStop(resource) 
if resource ~= getThisResource() then return end 
    for i=1, #Punished do 
        local exec = dbQuery(Database, "DROP TABLE IF EXISTS"..Punished[i][2]) 
        local result = dbPoll(exec, -1) 
        local query = dbQuery(Database, "CREATE TABLE "..Punished[i][2].." (Serial varchar, StartTime int, EndTime int, Duration int, PlayerName varchar, ResponsibleName varchar, Reason varchar );") 
        local result = dbPoll(query, -1) 
        local dataSaving = dbQuery(Database, "INSERT INTO "..Punished[i][2].." (Serial, StartTime, EndTime, Duration, PlayerName, ResponsibleName, Reason) VALUES ("..Punished[i][2]..","..Punished[i][3]..","..Punished[i][4]..","..Punished[i][5]..","..Punished[i][6]..","..Punished[i][7]..","..Punished[i][8]..")") 
        local result = dbPoll(query, -1) 
    end 
end 
addEventHandler("onResourceStop", resourceRoot, SaveDownOnResourceStop) 

EDIT: How can i return all values from all tables with select? i cant find a way to run over all existing tables..

Like: SELECT * FROM *

Don't forget; your database is built based on a database. There is a table holding all of your data, the table which holds the names of all your tables is; "all_tables" ( only on oracle systems as far as I know ). So you can just select it like this;

SELECT `table_name` FROM `all_tables` 

There are 4 tables in total that holds data like that, they are as following;

tabs 
dba_tables 
all_tables 
user_tables 

[.:HyPeX:.]

If it is only on oracle systems, what use is that for?

[tosfera]

If it is only on oracle systems, what use is that for?

Oracle are like... 90% of the servers. There are a few non-supported mysql platforms out there which aren't based on Oracle.

[.:HyPeX:.]

If it is only on oracle systems, what use is that for?

Oracle are like... 90% of the servers. There are a few non-supported mysql platforms out there which aren't based on Oracle.

I meant: Heyooo, i think we are in MTA, Windows/Linux

[tosfera]

If it is only on oracle systems, what use is that for?

Oracle are like... 90% of the servers. There are a few non-supported mysql platforms out there which aren't based on Oracle.

I meant: Heyooo, i think we are in MTA, Windows/Linux

Oh I think you don't get it, Oracle isn't an operating system. Oracle is like... the fundament of SQL. Just try to select from the tables and see if you have the right permissions for it to select from it.

[.:HyPeX:.]

SELECT * FROM `all_tables`;

Error1: could not prepare statment (1 no such table: all_tables)

Just tried it. http://www.w3schools.com/sql/trysql.asp ... select_all

[Castillo]

SELECT name FROM sqlite_master WHERE type='table' 

That query will return all the table names.

[.:HyPeX:.]

Wow thanks!!! working!!

on MTA, wich tables are original? so i can remove them off by start.

[Castillo]

As far as I know, it has no default tables.

[.:HyPeX:.]

Great, then it should be like this?

  
local Savings = {} 
function test() 
local dbqery = dbQuery(db, "SELECT name FROM sqlite_master WHERE type='table'") 
local table = dbPoll(dbquery, -1) 
for i,v in ipairs(table) do 
table.insert(Savings, v) 
outputChatBox("Table Found! Name: "..v) 
end 
end 
  

[tosfera]

Oh, I see. You are using sqlite... my fault. ^^"

Just a tip, don't call it table. Because table is also the name for... tables / arrays. Will be confusing and might create some errors.

[.:HyPeX:.]

Oh, I see. You are using sqlite... my fault. ^^"

Just a tip, don't call it table. Because table is also the name for... tables / arrays. Will be confusing and might create some errors.

It was a quick typing down, not really like that

But it is then, okay?