SQLite - few questions

[ds1-e]

Hey, i'm beginning experimenting with sqlite stuff, and i have few questions about that.

1.

https://wiki.multitheftauto.com/wiki/DbPoll

Quote

• timeout: How many milliseconds to wait for a result. Use 0 for an instant response (which may return nil). Use -1 to wait until a result is ready. Note: A wait here will freeze the entire server just like the executeSQL* functions

I don't clearly understand, what means that it will freeze entire server? It is some kind of lag, until function is executed? If so, then how many ms would be a decent value here?

2. 

https://wiki.multitheftauto.com/wiki/DbExec

This function is meant to change something in database, and it doesn't give any return in back? For example creating tables if they not exist? Should be always used within dbFree?

3.

https://wiki.multitheftauto.com/wiki/DbQuery

This one should be used within dbPoll to get result? Plus dbFree in case of don't using dbPoll?

4.

https://wiki.multitheftauto.com/wiki/DbFree

About dbFree, what if this function should be executed, but it wouldn't be executed?

5.

I can't access debugdb for some reason, probably is due of ACL, however i couldn't find anything related with them in ACL, or i simply removed/skipped it.

6.

https://wiki.multitheftauto.com/wiki/DbPrepareString

This function is meant to help and prevent SQL injections. So it should be all the time i guess? As i see this function also allows to create a big query with loop.

 

[savour]

11 hours ago, majqq said:

I don't clearly understand, what means that it will freeze entire server? It is some kind of lag, until function is executed? If so, then how many ms would be a decent value here?

 

It means that it will wait for the results from the database, then continue executing, however, if the function didn't receive any value from the query during the `timeout` it will return nil, then continue the execution.

for SQLite, you shouldn't worry about that (since it is on the same server, you almost get an instant response) so an ideal value should be -1 (no timeout) if you are dealing with a well-made database and healthy queries.

11 hours ago, majqq said:

https://wiki.multitheftauto.com/wiki/DbExec

This function is meant to change something in database, and it doesn't give any return in back? For example creating tables if they not exist? Should be always used within dbFree?

 

This function, dbExec, doesn't return any value related to the query, it just executes the query, so you don't need a dbFree. (also, dbFree is used on a dbQuery return)

and yea, you should use it when you expect no return from it, like creating tables of not exists, updating, dropping/deleting.

11 hours ago, majqq said:

https://wiki.multitheftauto.com/wiki/DbQuery

This one should be used within dbPoll to get result? Plus dbFree in case of don't using dbPoll?

 

Use dbPoll if you're expecting a result, dbFree if you don't expect a result (just like using dbExec). i will make it clearer:

-- Here, you don't expect a result
local qh = dbQuery(connection, "CREATE TABLE IF NOT EXISTS table_name")
dbFree(qh)
-- This is the same as
dbExec(connection, "CREATE TABLE IF NOT EXISTS table_name")

-- Here, you are expecting a result
local qh = dbQuery(connection, "SELECT * FROM table_name")
local result = dbPoll(qh, -1)	-- (When using -1 timeout, you don't really need to use dbFree)

However,  you should use dbFree when setting a 0+ timeout with a chance of failure.

12 hours ago, majqq said:

I can't access debugdb for some reason, probably is due of ACL, however i couldn't find anything related with them in ACL, or i simply removed/skipped it.

 

It's a server-console command, which means that it's only available on the server's command prompt

12 hours ago, majqq said:

https://wiki.multitheftauto.com/wiki/DbPrepareString

This function is meant to help and prevent SQL injections. So it should be all the time i guess? As i see this function also allows to create a big query with loop.

 

You should always use it in general. it makes quotes and other stuff to make sure it doesn't contain a second statement (which is the basic SQL Injection) is there. for the loops thing, it return a string and you concatenate it, so you can print it to see the difference, you will understand it more this way.

one more thing, string buffering is extremely bad for the performance, instead use a table and concatenate it later.

[ds1-e]

22 hours ago, savour said:

It means that it will wait for the results from the database, then continue executing, however, if the function didn't receive any value from the query during the `timeout` it will return nil, then continue the execution.

for SQLite, you shouldn't worry about that (since it is on the same server, you almost get an instant response) so an ideal value should be -1 (no timeout) if you are dealing with a well-made database and healthy queries.

This function, dbExec, doesn't return any value related to the query, it just executes the query, so you don't need a dbFree. (also, dbFree is used on a dbQuery return)

and yea, you should use it when you expect no return from it, like creating tables of not exists, updating, dropping/deleting.

Use dbPoll if you're expecting a result, dbFree if you don't expect a result (just like using dbExec). i will make it clearer:

-- Here, you don't expect a result
local qh = dbQuery(connection, "CREATE TABLE IF NOT EXISTS table_name")
dbFree(qh)
-- This is the same as
dbExec(connection, "CREATE TABLE IF NOT EXISTS table_name")

-- Here, you are expecting a result
local qh = dbQuery(connection, "SELECT * FROM table_name")
local result = dbPoll(qh, -1)	-- (When using -1 timeout, you don't really need to use dbFree)

However,  you should use dbFree when setting a 0+ timeout with a chance of failure.

It's a server-console command, which means that it's only available on the server's command prompt

You should always use it in general. it makes quotes and other stuff to make sure it doesn't contain a second statement (which is the basic SQL Injection) is there. for the loops thing, it return a string and you concatenate it, so you can print it to see the difference, you will understand it more this way.

one more thing, string buffering is extremely bad for the performance, instead use a table and concatenate it later.

Since i've just started my experience with SQLite (the time has come ), i am sorry about probably lots of incoming questions. Gotta learn when and where i should use certain functions. Excuse me in case of missing them, or using them in wrong time.

Here comes first question, i would need to get some basic data on resource start about player, in this case nick, serial and IP.

local database = dbConnect("sqlite", "db/database.db")

--[[***************************************************]]

function testFunction()
	local players = getElementsByType("player")
	for i = 1, #players do
		local player = players[i]
		local player_nick = getPlayerName(player)
		local player_serial = getPlayerSerial(player)
		local player_ip = getPlayerIP(player)

		local dbQ = dbQuery(database, "SELECT * FROM `players` WHERE `serial` = ?", player_serial)
		local dbR = dbPoll(dbQ, -1)

		if dbR and #dbR == 0 then
			dbExec(database, "INSERT INTO `players` (`name`, `serial`, `ip`) VALUES (?, ?, ?)", player_nick, player_serial, player_ip)
		end
	end
end

--[[***************************************************]]

function onResourceStart()
	if database then
		local qh = dbExec(database, "CREATE TABLE IF NOT EXISTS `players` (`name` TEXT, `serial` TEXT, `ip` text)")
		testFunction()
		outputDebugString("Established connection to database.", 0, 255, 127, 0)
	else
		outputDebugString("Failed to establish connection.", 0, 255, 127, 0)
		stopResource(getThisResource())
	end
end
addEventHandler("onResourceStart", resourceRoot, onResourceStart)

I'm just worried about dbExec calls, f.e when 40 players are online on server, i am not sure if this could be done at once.

[IIYAMA]

52 minutes ago, majqq said:

I'm just worried about dbExec calls, f.e when 40 players are online on server, i am not sure if this could be done at once.

dbExec

should be fine, your server might experience performance impact, but shouldn't freeze in a badly way.

 

dbQuery

is a different story. This is where you should use the callback function. You do not want your resource to wait for the database [doing something ...] > while starting up. The callback function will inform you when the player is ready to play.

[ds1-e]

21 minutes ago, IIYAMA said:

dbExec

should be fine, your server might experience performance impact, but shouldn't freeze in a badly way.

 

dbQuery

is a different story. This is where you should use the callback function. You do not want your resource to wait for the database [doing something ...] > while starting up. The callback function will inform you when the player is ready to play.

What about this?

https://wiki.multitheftauto.com/wiki/DbPrepareString

savour mentioned it:

Quote

one more thing, string buffering is extremely bad for the performance, instead use a table and concatenate it later.

Could you show me an example, because i'm not sure how it should be done.

[IIYAMA]

33 minutes ago, majqq said:

Could you show me an example, because i'm not sure how it should be done.

I copied the first from the wiki page:

serialsToUse = { "111", "222", "333" }

local queryString = dbPrepareString( connection, "SELECT * FROM `player_info` WHERE true" )
for _,serial in ipairs(serialsToUse) do
    queryString = queryString .. dbPrepareString( connection, " AND `serial`=?", serial )
end
local handle = dbQuery( connection, queryString )

 

 

And changed it a bit

serialsToUse = { "111", "222", "333" }

local queryTable = {}

queryTable[#queryTable + 1] = dbPrepareString( connection, "SELECT * FROM `player_info` WHERE true" )

for _,serial in ipairs(serialsToUse) do
    queryTable[#queryTable + 1] = dbPrepareString( connection, " AND `serial`=?", serial )
end

local handle = dbQuery( connection, table.concat(queryTable) )

 

The performance increasement is exponential. Which depends on the content.

 

With just 2 strings, the performance will probably not getting any better. (worse)

String concatenate * strings VS (table index: .concat) + function call

 

 

 

 

Edited September 7, 2019 by IIYAMA

[savour]

6 hours ago, majqq said:

Here comes first question, i would need to get some basic data on resource start about player, in this case nick, serial and IP.

When you poll the query, it returns a table including other tables, each one represents a row, like this:

local dbQ = dbQuery(database, "SELECT * FROM `players` WHERE `serial` = ?", player_serial)
local dbR = dbPoll(dbQ, -1)
-- in this case you're only expecting one row so the player will be dbR[1]
serial = dbR[1].serial
name = dbR[1].name

-- If you're dealing with multiple entries:
local dbQ = dbQuery(database, "SELECT * FROM `players`")
-- This will result:
{
  {name = "player1", serial="serial1", ...},
  {name = "player2", serial="serial2", ...},
  -- and so on
}
  

 

About the dbExec, it will almost have no effect on the performance in your case, the query when the resource start will take some milliseconds ( kind of unnoticeable server lag ), so it should be fine. also you can test the performance by the getTickCount function to make sure everything is good

[ds1-e]

18 hours ago, IIYAMA said:

I copied the first from the wiki page:

serialsToUse = { "111", "222", "333" }

local queryString = dbPrepareString( connection, "SELECT * FROM `player_info` WHERE true" )
for _,serial in ipairs(serialsToUse) do
    queryString = queryString .. dbPrepareString( connection, " AND `serial`=?", serial )
end
local handle = dbQuery( connection, queryString )

 

 

And changed it a bit

serialsToUse = { "111", "222", "333" }

local queryTable = {}

queryTable[#queryTable + 1] = dbPrepareString( connection, "SELECT * FROM `player_info` WHERE true" )

for _,serial in ipairs(serialsToUse) do
    queryTable[#queryTable + 1] = dbPrepareString( connection, " AND `serial`=?", serial )
end

local handle = dbQuery( connection, table.concat(queryTable) )

 

The performance increasement is exponential. Which depends on the content.

 

With just 2 strings, the performance will probably not getting any better. (worse)

String concatenate * strings VS (table index: .concat) + function call

 

 

 

 

Is there any way to store whole table with items in column? Instead of doing x columns for every item?

local cache = {}
local player = getPlayerFromName("majqq")

cache[player] = { -- item, count
	["First item"] = 1,
	["Second item"] = 3,
	["Third item"] = 4,
}

 

[IIYAMA]

1 hour ago, majqq said:

Is there any way to store whole table with items in column? Instead of doing x columns for every item?

local cache = {}
local player = getPlayerFromName("majqq")

cache[player] = { -- item, count
	["First item"] = 1,
	["Second item"] = 3,
	["Third item"] = 4,
}

 

That is normally not recommend, because you put a limit on your database. Your database will consider your data as text. (Or blob) But sure it is fine if you are not going to use your db to do stuff with the data.

 

Columns should be used for properties and properties only. So if a player has multiple items. We need two tables, and not more columns. Each new item should a row in the second table.

 

 

This page has a good example of how you would bind items/orders to a person:

https://www.w3schools.com/sql/sql_foreignkey.asp

 

[savour]

IIYAMA's answer is the ideal way to handle that, storing items in a separate table, or use add another columns to your existing table. you can still store a whole table at one cell by the way but maybe you will find it a little buggy. you can store the table in a cell as JSON (text) using toJSON(table), and when you retrieve it use fromJSON()

[ds1-e]

8 hours ago, savour said:

IIYAMA's answer is the ideal way to handle that, storing items in a separate table, or use add another columns to your existing table. you can still store a whole table at one cell by the way but maybe you will find it a little buggy. you can store the table in a cell as JSON (text) using toJSON(table), and when you retrieve it use fromJSON()

I would like to try this way, but what do u mean by saying "a little buggy"?

@IIYAMA

I have question about dbPrepareString.

serialsToUse = { "111", "222", "333" }

local queryString = dbPrepareString( connection, "SELECT * FROM `player_info` WHERE true" )
for _,serial in ipairs(serialsToUse) do
    queryString = queryString .. dbPrepareString( connection, " AND `serial`=?", serial )
end
local handle = dbQuery( connection, queryString )

Not sure if i understand it correctly.

"SELECT * FROM `player_info` WHERE true"

Select everything from `player_info` table, where true? (what is true exactly for?)

And later concatenate string with using serials (available in serialsToUse) as parameter.

[Hugos]

4 hours ago, majqq said:

I would like to try this way, but what do u mean by saying "a little buggy"?

@IIYAMA

I have question about dbPrepareString.

serialsToUse = { "111", "222", "333" }

local queryString = dbPrepareString( connection, "SELECT * FROM `player_info` WHERE true" )
for _,serial in ipairs(serialsToUse) do
    queryString = queryString .. dbPrepareString( connection, " AND `serial`=?", serial )
end
local handle = dbQuery( connection, queryString )

Not sure if i understand it correctly.

"SELECT * FROM `player_info` WHERE true"

Select everything from `player_info` table, where true? (what is true exactly for?)

And later concatenate string with using serials (available in serialsToUse) as parameter.

Do you need to get the player 's serial number from the DB?

[IIYAMA]

4 hours ago, majqq said:

Select everything from `player_info` table, where true? (what is true exactly for?)

True is true. It means just, yes go add it. It has probably been added to not have to add another Lua IF statement for the AND sql keyword.

"true"  ..  " and serial = 3442"

Or if the table is empty.

 

Anyway, the example is correct, the query on the other hand doesn't looks like it is correct:

true and serial = 3442 and serial = 3663 and serial = 4532

 

Shouldn't this be more logic?

serial = 2332 or serial = 3442 or serial = 2324

It can be my imagination...

 

 

 

 

 

 

Edited September 9, 2019 by IIYAMA

[Hugos]

I use the plugin "mta_mysql", and I write a similar function now (I want to know the player 's serial number and compare it to the one stored in the database)...

You Can try it...

local username = getPlayerName(source) --We learn the player's nickname
local qh_account = dbQuery(db, "SELECT * FROM accounts WHERE username=? LIMIT 1", username)
--We find a player with such a nickname in the DB (I have fields with a name called "username")
local result_account = dbPoll(qh_account, -1)[1]
if result_account then
	local serial = tostring(result_account.serial) --The serial number from a DB
	local u_serial = getPlayerSerial(source) --Current Player Serial Number
	if (serial == u_serial) then --compare
    	--if number does coincides.
	else
		 --if number does not coincides.
	end

P.S: Now I only have a question on how to record the player number if it does not match and is equal to 0. if you find a solution, please write.

[savour]

11 hours ago, majqq said:

I would like to try this way, but what do u mean by saying "a little buggy"?

 

Someone told me that he had bugs retrieving the false values from a json table (makes the value nil, which results of removing it from the Lua table). I didn't really test that bug but it should be fine, you can make some work around if you encountered it, not fatal bugs at the end so you are good to go (still, columns or another table are easier)

[ds1-e]

20 hours ago, savour said:

Someone told me that he had bugs retrieving the false values from a json table (makes the value nil, which results of removing it from the Lua table). I didn't really test that bug but it should be fine, you can make some work around if you encountered it, not fatal bugs at the end so you are good to go (still, columns or another table are easier)

Oh. I will experiment more soon.

@IIYAMA thanks for sharing this site, i've just noticed that it contains a lot of SQL Stuff which would help me in learning

[IIYAMA]

4 hours ago, majqq said:

Oh. I will experiment more soon.

@IIYAMA thanks for sharing this site, i've just noticed that it contains a lot of SQL Stuff which would help me in learning

To be honest, I am trying to avoid using that site. It is most of the time missing critical information, especially for semantic HTML.

There is only 1 thing that I like of it, and that is the simplicity of the information.

 

I normally use MDN(Mozilla Developer Network) for everything, but they do not have SQL docs.

But it seems like they recommend these sites on their website:

https://sqlzoo.net/wiki/SQL_Tutorial

http://www.tutorialspoint.com/sql/

Edited September 10, 2019 by IIYAMA

[ds1-e]

11 hours ago, IIYAMA said:

To be honest, I am trying to avoid using that site. It is most of the time missing critical information, especially for semantic HTML.

There is only 1 thing that I like of it, and that is the simplicity of the information.

 

I normally use MDN(Mozilla Developer Network) for everything, but they do not have SQL docs.

But it seems like they recommend these sites on their website:

https://sqlzoo.net/wiki/SQL_Tutorial

http://www.tutorialspoint.com/sql/

It is possible to create 2-3 tables using dbExec once?

[IIYAMA]

3 hours ago, majqq said:

It is possible to create 2-3 tables using dbExec once?

Hmm I don't think that is possible. Not because SQL can't do it, but the dbExec function clearly says "query" and not "queries".

If it is supported, then it would look a bit like this:

https://stackoverflow.com/questions/19239743/create-multiple-tables-using-single-sql-script-file

 

Also keep in mind that combining arguments (Lua) for multiple tables (SQL) have a potential risk of applying variables to the wrong table. (That is if you use variables + ?)

{ARGUMENT LIST + ARGUMENT LIST} = unpack(TABLE) = NEW ARGUMENT LIST

 

[ds1-e]

4 hours ago, IIYAMA said:

Hmm I don't think that is possible. Not because SQL can't do it, but the dbExec function clearly says "query" and not "queries".

If it is supported, then it would look a bit like this:

https://stackoverflow.com/questions/19239743/create-multiple-tables-using-single-sql-script-file

 

Also keep in mind that combining arguments (Lua) for multiple tables (SQL) have a potential risk of applying variables to the wrong table. (That is if you use variables + ?)

{ARGUMENT LIST + ARGUMENT LIST} = unpack(TABLE) = NEW ARGUMENT LIST

 

Thanks!

Could you suggest some community scripts which are using SQLite in good way. I would prefer to learn more from script itself.

Edited September 11, 2019 by majqq

[IIYAMA]

1 hour ago, majqq said:

Thanks!

Could you suggest some community scripts which are using SQLite in good way. I would prefer to learn more from script itself.

Not really, I do not look at community resources that much.

But there is a book called SQL in 10 minutes, which has tons of good examples in it.

Try to find yourself a copy of it on the internet.

 

[ds1-e]

5 hours ago, IIYAMA said:

Hmm I don't think that is possible. Not because SQL can't do it, but the dbExec function clearly says "query" and not "queries".

If it is supported, then it would look a bit like this:

https://stackoverflow.com/questions/19239743/create-multiple-tables-using-single-sql-script-file

 

Also keep in mind that combining arguments (Lua) for multiple tables (SQL) have a potential risk of applying variables to the wrong table. (That is if you use variables + ?)

{ARGUMENT LIST + ARGUMENT LIST} = unpack(TABLE) = NEW ARGUMENT LIST

 

I used dbPrepareString in other way (adding items only by modyfing table), could you check if everything is correct?

Looks like table and columns created normally.

local query_table = {}
query_table[#query_table + 1] = dbPrepareString(serverTable.db, "CREATE TABLE IF NOT EXISTS `Items` (`Serial`, ")

for i = 1, #serverTable.config.save_items do
	local item = serverTable.config.save_items[i]
	local check_index = i == #serverTable.config.save_items and ")" or ", "
	query_table[#query_table + 1] = dbPrepareString(serverTable.db, "`"..item.."` INT"..check_index)
end

dbExec(serverTable.db, table.concat(query_table))

[IIYAMA]

2 hours ago, majqq said:

Looks like table and columns created normally.

I can't judge this very well, the best moment of debugging this,  is when the query is concatenated.

iprint(table.concat(query_table)))

Also a query string is something you can put in to a validator:

https://www.eversql.com/sql-syntax-check-validator/

 

You might want to add spaces, just in case some values are wrongly concatenated.

 table.concat(query_table, " "))

 

[ds1-e]

7 hours ago, IIYAMA said:

I can't judge this very well, the best moment of debugging this,  is when the query is concatenated.

iprint(table.concat(query_table)))

Also a query string is something you can put in to a validator:

https://www.eversql.com/sql-syntax-check-validator/

 

You might want to add spaces, just in case some values are wrongly concatenated.

 table.concat(query_table, " "))

 

iprint result:

And i've also check that in SQLite Browser.

[ds1-e]

17 hours ago, IIYAMA said:

I can't judge this very well, the best moment of debugging this,  is when the query is concatenated.

iprint(table.concat(query_table)))

Also a query string is something you can put in to a validator:

https://www.eversql.com/sql-syntax-check-validator/

 

You might want to add spaces, just in case some values are wrongly concatenated.

 table.concat(query_table, " "))

 

Also, i need to ask about that, it's possible to obtain data from 2 tables in one query? In my case `Players` and `Items`?