SQLite Using your SQLite database!

[7881374]

Thanks For Your Works Man

 

[Nightmare25]

@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:

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).

PLX HELP when i try to put the quarry and press f5 appears a msg
"abort due to constrait violation" pleaze help me i wanna change the flag of a god =/

 

[Nightmare25]

I tryed and i did it

but now i need help with doing the character i put all the things correct but still appearing error =/ what should i do help me plz

 

[Khaos]

Pedro, thanks for posting info on this great program! Helped me design a database while I was working on something far more mundane than OTServ.

I necromanced this thread because I thought it might be useful to comment this:

The down side of SQLite is the lack of Multi-INSERTs. With MySQL, you could use this:

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).

It's important to remember that if you store a lot of data, you should use SQLite transactions. That way, the data is stored in an unoptimized way until you commit it into database. So:

BEGIN TRANSACTION;
INSERT INTO ...
<10000x inserts, updates, whatever's>
INSERT INTO ...
END TRANSACTION;

(or COMMIT instead of END TRANSACTION).

When Outcast was converting tibia.dat into SQLite for "easier access" it was quite helpful to first start transaction. From about 5 minutes to import items, the time fell to about 5 seconds.

Oh, and don't forget to VACUUM after a long series of database modifications. It optimizes the database a bit.

 

[Guitar Freak]

Guys I have a question and Im not quite sure if it belongs here but I dont know where else could it go.

Im using SQLite on a TFS 0.2 based server and I would like to customize my Account Manager, like I want to be able to set myself the starting eq of new players, how do I do it here?

In XML it used to be easy (or at least possible) because I only had to edit the Account Manager.xml file and choose the eq IT had, and every new character would have that same eq. But Im very newbish on SQL and have no idea how to do this here.

EDIT: Btw another thing, how do I see a certain's player equipment/items? Like in XML where you could do it like I said before, just by editing the file you could see all his items+depot items and all, how do I do it here? Is it possible?

Please if you know the answer of this let me know, Id greatly appreciate it.
Thanks in advance.

 

[Nooben nr1]

funny thing is u said "dont need to hit save anywhere", I've done my God 3 times over and soon as I check another table it disapears.

 

[Nooben nr1]

I couldn't create another GM/God acc, I made 1 god and 1 GM on one account but then I tried to do another GM for my friend but it doesn't work, the char doesn't save in the player table :s

 

[Dodekus]

This helped me allot.
Thank you!

 

[Dodekus]

I couldn't create another GM/God acc, I made 1 god and 1 GM on one account but then I tried to do another GM for my friend but it doesn't work, the char doesn't save in the player table :s

You have to press that little green tick. "Post Edit"
Then it saves the thing you edited.

 

[sorcerers king]

Heeey but i have error! look!

So why i can't find the sqlite folder so i can open it like when i use mysql i can choose schema.sql?

 

[mpa]

I didn't know this.
When is it better to use mysql?

 

[Cute Fighter]

heey very nice tut it helped me a lot and i'm using now sqlite expert personal 1.7.82
But i have a problem
I get an error when I'm trying to save changed details of a player in the database :/ example: changed number in "looktype"
i get this error:
"Overflow while converting variant of type (Int64) into type Integer)"

I don't know whats wrong :/
i'm hoping u can help me =)

 

[Henkos]

It says the temple positon is wrong.. im using a roxor 8.42

 

[Eless_Dee]

i have sqlite and my account and character on my server. its home based still. how do i give my character admin powers or gm powers?

 

[induce]

annoyingggg simpleeee problem :[

By "groups" table, I suppose you mean group_id table? Anyways I finally got it working by changing the group_id to 6 (GOD) in accounts/data/group_id tab. As well as in the players section.