Jump to content

SQLite related questions


Recommended Posts

Greetings!

Just need few answers to few of my queries. But before i get to the questions, i would like to briefly explain what my motive is. So basically, i am trying to construct a fairly stable SQLite database management system kind of thingy. I have been going through the w3schools website for the past 3-4 days just to be sure of what i'm doing. Don't want to attempt doing something way out of my comprehension and then regretting even trying it out lol. Anyway, enough trashtalk. Here are my questions:

Q1) How would you want to organize your db files ? Would you allot all the tables to a single db file or would you segregate various tables among several db files and then use a unique identifier (Preferably an index value? :3) to make sure that a particular entry pertaining to say 'x' db file has a sort of connection with that in 'y' db file.

Q2) Should i be concerned about any limit in particular about the columns that i keep in a particular table? Well, i know there is no limit as such, but would you want to advice me to restrict the number of columns just to make sure that it doesn't alter my server's efficiency and performance ?

Q3) I have a very simple minded plan to sort out all the loading and saving of the data. I'm thinking about retrieving all the data from the db and then saving it as the player's element data. As a security measure, i will not allow any synchronization between the client and the server in this regard. So yeah, this is pretty much a basic approach so my question is that does this seem good enough or whether there is/are any alternative(s) to deal with something like this ?

Q4) Now i'm sure that the size of the db file depends on the playerbase but approximately what size should i expect for a server with a decent amount of players?

Q5) Do you have tips or suggestions that you would like to give me ?

Q6) Lastly, what does the fox really say? [ Feel free to ignore this one lol ]

- NeO_DUFFMAN [ Ex Level 8 Admin @ Team NeO Server ]

Link to comment
  • Moderators

Q1) How would you want to organize your db files ? Would you allot all the tables to a single db file or would you segregate various tables among several db files and then use a unique identifier (Preferably an index value? :3) to make sure that a particular entry pertaining to say 'x' db file has a sort of connection with that in 'y' db file.

There is no need to use multiple databases for a single gamemode. The queries would be harder than the queries you will perform on a single database. Using more that one database is for really specific cases.

So I really recommend you to use one database (even if you have 30+ tables in it)

Q2) Should i be concerned about any limit in particular about the columns that i keep in a particular table? Well, i know there is no limit as such, but would you want to advice me to restrict the number of columns just to make sure that it doesn't alter my server's efficiency and performance ?

Well not really but for me, if a table has more than 15 columns, it's almost sure that there are some columns that can be in another table.

Example: you have a table name "players" in which you have the following columns:

id|login|password|language|weaponSlot0|ammoSlot0|weaponSlot1|ammoSlot1| ... |weaponSlot12|ammoSlot12 

So there are 30 columns

You can easily simplify this by using another table you can call "weapons" or "weaponsInventory" in which you will get these columns:

id | playerid | weapon | ammos 

where playerid will be a foreign key of the id column from the "players" table

example of entries:

Table: players

|  id | login | password | language | 
|-----|-------|----------|----------| 
|  1  | duff  | thepass  |    en    | 

Table: weapons

  
|  id | playerid | weapon | ammos | 
|-----|----------|--------|-------| 
|  1  |     1    |    5   |   1   | 
|  2  |     1    |   35   |  130  | 

It means that the playerid 1 (duff in the player table) has 1 baseball bat and a rocket launcher with 130 rockets.

Q3) I have a very simple minded plan to sort out all the loading and saving of the data. I'm thinking about retrieving all the data from the db and then saving it as the player's element data. As a security measure, i will not allow any synchronization between the client and the server in this regard. So yeah, this is pretty much a basic approach so my question is that does this seem good enough or whether there is/are any alternative(s) to deal with something like this ?

myeah not a bad idea, but do not use to much triggers to get the players datas on the client-side.

Q4) Now i'm sure that the size of the db file depends on the playerbase but approximately what size should i expect for a server with a decent amount of players?

I don't really know about the size of sqlite database files but you will need a lot of datas to go over 500 MB or 1GB. So you don't have to worry about it unless you are storing it on 2GB harddrive :lol:

Q5) Do you have tips or suggestions that you would like to give me ?

Do not get all columns when you only need the datas in some specific columns (To get the money: "SELECT * FROM players WHERE login='duff';" and then only get the money or: "SELECT money FROM players WHERE login='duff';")

NEVER store the original passwords in the database, hash them (use sha256 for decent security and you can also "salt" it. More infos about salted hashes here). And when the players try to login, you will hash the password he sent and then check the hash in the database. If both are the same, it means that the password he just sent is the same as the one in the database (inserted when he registered).

You should never get the password from the database into your lua script. Let the SQL queries check it for you using this simple query:

"SELECT id, login, language FROM players WHERE login='duff' AND password='thepass';"

It will return the rows with only the value of id, login and laguage columns (don't use * because you don't want to get the password) where the login is duff and thepass is thepass.

If it returns you 1 row, then it means that it (SQLite in your case) found a row matching the WHERE closure so the login and password are good.

If it returns an empty result, then it means that the login or password aren't correct.

Always put an id column in all tables. It should be a PRIMARY KEY (the column that can be trusted to identify an entry/row. It should never be changed !). And enable the AUTO_INCREMENT on that column. This way, the id will be unique and will be automatically seted when using INSERT statement (so do not specify it when using the INSERT statement).

You can add a simple INDEX on columns you will often use in WHERE closure (on tables that will get really big like the players table). In the example above, it will be a good idea to set an INDEX on the login column.

You can set a UNIQUE index. It's an index that will drop queries that will try to insert a new row with a value in that column already used in another row.

- NeO_DUFFMAN [ Ex Level 8 Admin @ Team NeO Server ]

- Citizen [ Ex player of a random server ] (yeah no one care xD)

Link to comment

Thanks a lot Citizen. I really appreciate how you have answered all of my queries with such detail and with examples. Also, i'm going to take a note of all those tips that you have given me for use in the future. Atm, ill try working my way up by sorting out the loading-saving bit of the system within mta itself.

So I really recommend you to use one database (even if you have 30+ tables in it)

Alright then, i'm going to use a single db file to sort out everything. Doubt i'll be keeping more than 15 tables so i'm glad you said 30+ lol.

Well not really but for me, if a table has more than 15 columns, it's almost sure that there are some columns that can be in another table.

Example: you have a table name "players" in which you have the following columns:

id|login|password|language|weaponSlot0|ammoSlot0|weaponSlot1|ammoSlot1| ... |weaponSlot12|ammoSlot12 

So there are 30 columns

You can easily simplify this by using another table you can call "weapons" or "weaponsInventory" in which you will get these columns:

id | playerid | weapon | ammos 

where playerid will be a foreign key of the id column from the "players" table

example of entries:

Table: players

|  id | login | password | language | 
|-----|-------|----------|----------| 
|  1  | duff  | thepass  |    en    | 

Table: weapons

  
|  id | playerid | weapon | ammos | 
|-----|----------|--------|-------| 
|  1  |     1    |    5   |   1   | 
|  2  |     1    |   35   |  130  | 

It means that the playerid 1 (duff in the player table) has 1 baseball bat and a rocket launcher with 130 rockets.

That seems like a simpler way of finding a particular entry in another table using a common id from the first table. So i'll store the index (from the result of the first table) as the foreign id in other tables right ? [Edit: Nevermind, i quickly tested this out and it worked just like i thought it would]

I don't really know about the size of sqlite database files but you will need a lot of datas to go over 500 MB or 1GB. So you don't have to worry about it unless you are storing it on 2GB harddrive

Phew! In that case i guess i shouldn't be worrying. But do you think i should perhaps make a backup of the db every 30 minutes or perhaps 1 hour in case the server crashes ? ( Also, are there any chances of DB files getting corrupted somehow?)

- Citizen [ Ex player of a random server ] (yeah no one care xD)

Yeah i guess that was completely unnecessary and irrelevant lol....

Lastly, i was going through a SQLite tutorial on the forum and i came across this post of Callum. Not sure what he meant by appending 'LIMIT 1' to the end of the query cause i couldn't test this out in w3school's editor since it kept on giving me a syntax error. But apparently the MySQL syntax for this is -

SELECT column_name(s) 
FROM table_name 
LIMIT number; 

function test ( name ) 
    local query = dbQuery ( exports.misc:getConnection ( "server" ), "SELECT * FROM user_list WHERE username = ?", name ) 
    local result, numrows, errmsg = dbPoll ( query, -1 ) 
    if ( type ( result ) == "table" and #result == 0 or not result ) then 
        -- does not exist 
    else 
        -- exist 
    end 
end 

If you're only expecting one result, you should append 'LIMIT 1' to the end of the query, so that SQL will stop looping the database once it has 1 result. This can majorly improve performance.

So in case i want SQL to stop looping once i've have got my result, how can i do this by using 'LIMIT 1' ?

Link to comment
  • Moderators

Well idk much how sqlite works internally and how it is bound in the MTA Server code, but I think the file can't be corrupted if MTA developpers did it correctly using a detached thread so if the main thread (the mta server) crash, then the detached thread will still work untill the query he got has been done correctly. I can't verify it right now but backups aren't needed (or you won't use them to rollback the db if it got corrupted somehow because it will likely never happen). But if you need backups for another reason, then I would probably do one every 4 or 8 hours (so 8 or 4 backups a day).

The LIMIT keyword in a SQL query let you specify how much rows you want to get at most. So if you want at most 1 row, then the query will looks like this:

"SELECT * FROM user_list WHERE username = ? LIMIT 1" 

But strings must be surrounded with single quotes to be sure the query won't think it's a column or an internal variable name:

"SELECT * FROM user_list WHERE username = '?' LIMIT 1" 

Thanks a lot Citizen.

No problem, I wouldn't do it if you didn't make some work on your side before asking and you seems really interested in SQL stuff so yeah, it was a pleasure :wink:

Link to comment

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...