• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

SQLite Using your SQLite database!

Pedro B.

OpenTibia Official Dev
Joined
Aug 8, 2007
Messages
127
Reaction score
2
Location
Brazil
Well, before all, I must tell you that this tutorial was made based on OTS and not on TFS. If any moderator/admin or whatever feels that it should be edited to fit TFS, do it, I don't really care.

This is just a Copy-and-Paste from my tutorial on another forum.


I found that many people have trouble with MySQL servers, and keep with XML for that.

Well, I'll show you how to use SQLite.

But what is SQLite?
SQLite is a database file based on the SQL database language. Different from MySQL, SQLite isn't run from a server - but directly from a file on your computer! :)

Some servers, like TheForgottenServer or NOTS, support the SQLite well, while other doesn't(like Evolutions or YurOTS - THEY ARE SHIAT).


To enable your SQLite, be sure the server you are running supports SQLite and is compiled with SQLite.

Then, go to your config.lua and find this:
Code:
sql_type = ""
Perhaps it is "mysql" instead of "". Change it to "sqlite", if it isn't already. Or perhaps it is sqlType instead of sql_type.

Now, with your server a file called 'db.s3db' or 'forgottenserver.s3db'(in the case of TFS). If your server doesn't has it, then it probably doesn't supports SQLite.
Just keep in mind where the file is.


Well, if you don't really need to change anything on your database, we are done. Find in the config.lua "s3db". Be sure the name of the file is the name of your database file(the db.s3db) and that it is on the same folder of your server.

Then you can run it, it is done! :)

Whoa?! Just that?
Yes.

Where the accounts and the players are, if we aren't using XML?!
They are all on that s3db file. Not just that, that file stores the accounts, the players, the items, the skills, the storage values, the tiles and the houses.

LOL all that in one file?!?!?! No way!
Ya way, thats one of the advantages of SQLite: All-in-one ;)

But I can't open it on notepad! How can I change my chars? :/
Then you'll need a SQLite management tool. There are many out there, most of them must be purchased.

SQLite Expert Personal
The best tool I think there is out there, I love it.

official site: http://www.sqliteexpert.com
direct download link(Personal version, Pro version is paid): http://www.sqliteexpert.com/SQLiteExpertPersSetup.exe

After you download and install it(shouldn't be hard), you can run it.

Go to file->Open Database, and find your server folder, and then find your database. If you can't find it, select to open "Any file" instead of "Database files". Once you opened your db.s3db file, it should appear on the list at the left.

Select the 'accounts' table there, and then open the "Data" tab at the right, if it isn't already open.

To create an account, click on the plus('+') sign there. Now you will fill the fields at your own way.
No not fill the RecNo field, it is useless. the 'id' field is the account number. The rest you guess :p

After you're done, you can switch to the groups table(before adding any player).
Before adding anything, Go to Tools->Options(at the top). Go to the "Type Mappings" table.

Find the INT and the INTEGER declared type, and change their value to Largeint on the right side. So there won't be any issues after.

Now, back to the groups(hit 'Ok').
You can create any groups you wish for the players. If you don't have an idea about how to create groups, execute these queries on the database:
Code:
INSERT INTO "groups" ("name", "flags", "access", "maxdepotitems", "maxviplist") VALUES ("Player", 0, 0, 1000, 50);
INSERT INTO "groups" ("name", "flags", "access", "maxdepotitems", "maxviplist") VALUES ("Premium Player", 0, 0, 2000, 100);
INSERT INTO "groups" ("name", "flags", "access", "maxdepotitems", "maxviplist") VALUES ("Tutor", 16777216, 0, 1000, 50);
INSERT INTO "groups" ("name", "flags", "access", "maxdepotitems", "maxviplist") VALUES ("Premium Tutor", 16777216, 0, 2000, 100);
INSERT INTO "groups" ("name", "flags", "access", "maxdepotitems", "maxviplist") VALUES ("Gamemaster", 217768239050, 1, 2000, 100);
INSERT INTO "groups" ("name", "flags", "access", "maxdepotitems", "maxviplist") VALUES ("Senior Gamemaster", 269307846602, 2, 2000, 100);
INSERT INTO "groups" ("name", "flags", "access", "maxdepotitems", "maxviplist") VALUES ("Community Manager", 272227082232, 3, 2000, 100);

How do I execute a query????
With the 'groups' table select, go to the "SQL" tab(on the right screen). It has a green buttom with "SQL" written in it.

There, copy-and-paste the queries I just showed to you, and then hit F5. Now if you select the "Data" tab again, you'll see all your groups right there ;)

You can create a new group by hitting the plus('+') sign there.

What are the flags?
They are the acesses for the players so you can set if you want them to not attack something, or if you want monsters to ignore them, etc..

These flags are bit-based, so it obviously has some weird numbers. To know the right number you want, go to http://hem.bredband.net/johannesrosen/playerflags.html. This is a site made by SimOne to help people to know the flag value ;)

Now that you're done, you can create Players :D

Go to the players table. Now, hit the plus sign(again). Now start to fill each field follwing this list:
Code:
'id' = don't write anything
'name' = the character's name.
'account_id' = the acc of the character
'group_id' = the group of your character(1 = Player, 2 = P.A Player, 3 = Tutor, 4 = P.A Tutor, 5 = GM, 6 = Senior GM, 7 = CM, you can also use any group you created)
'premend' - don't mess with this. Leave it 0.
'sex' - 0 = female, 1 = male
'vocation' - the ID of the vocation of the char
'experience' - the exp of the char(ya rly)
'level' - the level of the char(yea)
'health' - the current char's health
'healthmax' - the max HP the player can handle
'mana' - the current mana
'manamax' - the max mana
'manaspent' - Mana already spent on spells. It resets back to 0 when you get a new mlvl.
'soul' - the current soul
'direction' - 0(north), 1(east), 2(south), 3(west)
'lookbody' - the color of the char's Primary Color
'lookfeet' - the color of the char's Detail color
'lookhead' - the color of the char's Head Color
'looklegs' - the color of the char's Secondary Color
'looktype' - the outfit the char is wearing
'posx', 'posy' and 'posz' - the X, Y and Z of the char, respectively. By setting them to '0' the char will login on it's temple
'lastlogin' and 'lastip' - Just set them 0, you don't need to mess with them
'save' - 0 means the char won't save(for war servers)
'conditions' - this one is messy. It is a BLOB, so you'll need to do something with it. Create an empty text file and save it anywhere. Then click on the 'BLOB' icon here and load that file.
'redskulltime' - Time until all the frags are cleaned
'redskull' - Does the char have a red skull? 1 for yes, 0 for no. The redskull will be there while the redskulltime is not 0.
'guildnick' - the nick the char has on his guild. The thing that appears between parentisis when looking at the char.
'loss_experience' - Default 10. How many exp(in %) the char will lose on death?
'loss_skills' - Default 10. Same as above, but for skills.
'loss_mana' - Default 10. Same as above, but for mana you spent on spells.
'rank_id' - The ID of the rank of this character in the guild he is
'town_id' - The ID of the character's living town, so the temple is set correctly. You set the town on the mapeditor.
'balance' - The current account money balance of the caracter, used when paying houses.

If you don't set anything to some fields, the default value will be set. Be aware: the conditions field does not has a default value, neither do the town_id, the account, the group, the name and the rank_id fields, so you must fill them.

Some servers may not have one of these fields, or some fields more. In this case, just ignore the extra fields since they will - probably - be filled in with their default value.

Then start messing around to see what happens :)
Remember that you don't need to hit 'Save' anywhere, and that there is no Ctrl+Z - so be aware.



You got the basic. Now STOP USING THE FUCKIN' XML!

why?!????? :(
Why? Let's see.

Everytime a player is loaded on the server(This means - when he receives a parcel while offline, everytime a guild-related or a vip-related function is called at the server, and everytime he logs in), the players.xml file is parsed, and after the player file is also parsed, both containing unuseful information.

Using a query is FAAAR faster than reading the 'players.xml'(to verify the player's existance) and then reading the player's xml file to get ALL his information(even the not needed).

With SQL queries, you only get informations that really matters for its purposes, and they are A LOT faster.

And also the weight of each file: the database file is WAAAAAY lighter than that bunch of XML files. Not to tell the all-in-one thing.

This is all you need to know about SQLite/SQL.

For further information:
www.sql.org
www.sqlite.org

cya ;D
 
Call me stupid or whatever, but what's the difference in SQL-lite and MySQL?

I've used MySQL earlier but is SQL-lite faster/easier for non-proffecional usage?
 
@up
Summing it up, it's what you said.

MySQL was mainly made for multiple server hosting. With MySQL you have to create a server and set it up. Also, when using it you must connect to the database.

Meanwhile, all you need for SQLite is a file, no setups, nothing really needed to run it. SQLite was added to OTServ to replace XML so people still don't need to connect to a MySQL server(even because it is stupid to connect to your own computer, when talking about performance).

The down side of SQLite is the lack of Multi-INSERTs. With MySQL, you could use this:
Code:
INSERT INTO "groups" ("name", "flags", "access", "maxdepotitems", "maxviplist") VALUES ("Player", 0, 0, 1000, 50), ("Premium Player", 0, 0, 2000, 100), ("Tutor", 16777216, 0, 1000, 50), ("Premium Tutor", 16777216, 0, 2000, 100), ("Gamemaster", 217768239050, 1, 2000, 100), ("Senior Gamemaster", 269307846602, 2, 2000, 100), ("Community Manager", 272227082232, 3, 2000, 100);

instead of the code on the topic, which is a lot faster. Though that doesn't really matters, since the only big difference is when sending about 10k queries(which is possible when saving house tiles, but that should only happen before closing the server).
 
really great it helped a lot i don't why more people haven't looked here and commented on this
 
M2 but 2 me it ses your not a game master ewen wen i hawe tild in the database that im a good... so can only jojn wit account manidger.... help plig xD
 
This was not made for TFS, you probably have to change the account type(or whatever it is).
 
Account type 5, and group id 3 on your character. You probaly forgot that.
 
Account type 5, and group id 3 on your character. You probaly forgot that.

Yea but ;) You need to set your account to acctype=5 and group_id 3 (default config) and your player to group_id 3 aswell
only after this will work well
 
Well didnt I just say that? o.o I runned sqlite for a couple days without any problems.

edit:
or well I remember i didnt add it, I just renamed an allready added God exampple. my bad :p
 
I dont get how to change Null to BLOB.. i duno where to load the text document from
 
I cant add any new players on sqlite.. Im using the 8.11-8.1 war server

Im on vista too.... If thats a prob?
 
Last edited:
i just cant seem to get the commands working,the suit changes,but teh commands dont work :(
 
There is one thing I don't understand, how do you host a website then? Where do the players make their accounts ?
 
I might be stupid, but I don't understand a shit out of it :D
Its nothing about ur tutorial, but I'm just new with this.
I got the server and the Sqlite expert. Now I open the server with it. So I go to accounts and then?? I just fill in the one wich is already there??
And where do I have to fill it in, do I have to delete the name and place it there or what?

Hope to hear something soon
 
Can you explain more specified on the "BLOB"-thing.. "Conditions" didn't get that right i think :c

Else really nice guide ;)
 
Last edited:
Back
Top