Item System Storage

Immolate · #1 12-23-2009, 12:51 PM

My task is to create an item system. I was just wondering what everybody's opinions are on the storage of data. I'm not too worried about your caching opinions but if you want to make them known, I'm more than happy to take advise.

Twinny · #2 12-23-2009, 01:22 PM

For me, both files and SQL works since I load them into a DBNPC. Both ways work since it's a one off read. SQL seemed pretty slow when dealing with alot of requests for me (which was really weird) so I wouldn't recommend storing and reading items out of it constantly.... works really well for logs though =D

For ease of use (and the fact I don't use the files live) I recommend file based

Immolate · #3 12-23-2009, 01:36 PM

Quote:

Originally Posted by Twinny

For me, both files and SQL works since I load them into a DBNPC. Both ways work since it's a one off read. SQL seemed pretty slow when dealing with alot of requests for me (which was really weird) so I wouldn't recommend storing and reading items out of it constantly.... works really well for logs though =D

For ease of use (and the fact I don't use the files live) I recommend file based

I guess that could work pretty well

Twinny · #4 12-23-2009, 01:41 PM

Quote:

Originally Posted by Immolate

I guess that could work pretty well

Truth be told, could just skip the loading part and code it straight into the dbnpc

Immolate · #5 12-23-2009, 01:59 PM

Quote:

Originally Posted by Twinny

Truth be told, could just skip the loading part and code it straight into the dbnpc

That's what I used to do when I only needed to create and manipulate items. I'm thinking loading from text-files into a DBNPC cache would make things easier when people want to manipulate items in the event I'm not around.

Though, it'd still be nice to hear what other people have to say on the matter.

Chompy · #6 12-23-2009, 02:05 PM

I have to say files. Easier to manage and work with if you are offline (You can write up items and all that stuff when you're missing an internet connection. Just upload them when you get internet back.)
I usually just store all the information in files, and then cache them for faster access.

It's just so much easier doing object.loadvars(filepath), instead of having to hazzle with requests and such with SQL..

It's personal preference to a certain degree though.

Immolate · #7 12-23-2009, 02:08 PM

Quote:

Originally Posted by Chompy

I have to say files. Easier to manage and work with if you are offline (You can write up items and all that stuff when you're missing an internet connection. Just upload them when you get internet back.)
I usually just store all the information in files, and then cache them for faster access.

It's just so much easier doing object.loadvars(filepath), instead of having to hazzle with requests and such with SQL..

It's personal preference to a certain degree though.

Thanks for your input

fowlplay4 · #8 12-23-2009, 05:45 PM

Files + DB Cache for Base Arc Data, and SQLite for storing player's item info (quantity, custom modifiers, etc.).

It's so much easier to do trades with SQLite, since you can just make the updates in one transaction, then have them both reload their items. Plus it makes the backing up of player data a simple one file backup.

xXziroXx · #9 12-23-2009, 05:46 PM

SQLite is by far the easiest and most powerful way of doing it.

Chompy · #10 12-23-2009, 06:41 PM

Quote:

Originally Posted by xXziroXx

SQLite is by far the easiest and most powerful way of doing it.

Could you elaborate on that?

xXziroXx · #11 12-23-2009, 06:46 PM

Quote:

Originally Posted by Chompy

Could you elaborate on that?

Not too much to elaborate on. SQLite database is a huge total filesize decrease comparing to plain files, it's faster loading/altering of data, it's *A LOT* easier to find specific data across the entire table then searching through all files. And the biggest pro of them all: save one, that's right - ONE, file on your computer and your entire servers database is backed up.

I could most probably list a lot more reasons why SQLite is superior, but the above ones are the ones that convinced me.

Chompy · #12 12-23-2009, 06:49 PM

Quote:

Originally Posted by xXziroXx

Not too much to elaborate on. SQLite database is a huge total filesize decrease comparing to plain files, it's faster loading/altering of data, it's *A LOT* easier to find specific data across the entire table then searching through all files. And the biggest pro of them all: save one, that's right - ONE, file on your computer and your entire servers database is backed up.

I could most probably list a lot more reasons why SQLite is superior, but the above ones are the ones that convinced me.

So you would store all information that is important or used by all of your systems in one SQL database?

Do you only use SQL or some combo as Jerret described in his post?

Edit; What if you have a player that had it's player data glitched, and you need to restore their old values from the old SQL backup, what would you do then? Install a third party software to view the table wanted and then manually query a change in the current SQL DB with the old backup data? (Seeing as requestsql2() isn't working as far as I've seen)

SKJF91 · #13 12-23-2009, 06:50 PM

For some reason, SQLite seems to have a high cpu usage on a few queries with graal though :/

xXziroXx · #14 12-23-2009, 06:57 PM

Quote:

Originally Posted by Chompy

So you would store all information that is important or used by all of your systems in one SQL database?

I so far store items, items owned by players, skills, quests, spells and recipes in my SQLite database. So yes, I guess you could say I store all important information in it.

Quote:

Originally Posted by SKJF91

For some reason, SQLite seems to have a high cpu usage on a few queries with graal though :/

Care to show examples? Only time I've experienced that is when I used SQLite queries in a very bad way (I've learned how to properly work with it since) or excessive use of VACUUM (which obviously gives high CPU usage).

Immolate · #15 12-23-2009, 07:13 PM

Quote:

Originally Posted by xXziroXx

Only time I've experienced that is when I used SQLite queries in a very bad way (I've learned how to properly work with it since) or excessive use of VACUUM (which obviously gives high CPU usage).

It would be really handy if you could provide tips and examples on how to properly work with it, since all I know is the basic syntax.

SKJF91 · #16 12-23-2009, 07:57 PM

Quote:

Originally Posted by xXziroXx

Care to show examples? Only time I've experienced that is when I used SQLite queries in a very bad way (I've learned how to properly work with it since) or excessive use of VACUUM (which obviously gives high CPU usage).

Just simple queries, inserting, selecting etc.. - and I know how to use proper queries such as JOIN etc although i'm unsure if sqlite supports all of that like mysql does.

salesman · #17 12-23-2009, 08:21 PM

Quote:

Originally Posted by SKJF91

Just simple queries, inserting, selecting etc.. - and I know how to use proper queries such as JOIN etc although i'm unsure if sqlite supports all of that like mysql does.

I'm not convinced that the CPU usage for SQL queries is entirely accurate.

coreys · #18 12-23-2009, 08:50 PM

I find files to be much easier than SQL, especially ini files.

From experiencing both, I think I'd have to go with Jerrets suggestion though, I'd use ini files for item data, and SQL for individual player data.

I personally think SQL is being way over estimated by everyone lately. It surely is very useful, especially with nice tools like Chris Vimes SQL Explorer, but it's not the perfect, beat all solution.

Crow · #19 12-23-2009, 08:55 PM

Quote:

Originally Posted by coreys

but it's not the perfect, beat all solution.

I'd actually say it is.

cbk1994 · #20 12-23-2009, 08:57 PM

On Era Dev we're using files for storing item data (items/weapons/guns/ak47.txt, for example), and then using SQL to store individual player data, e.g. the items the player has and the quantity of the item they have. If you index it properly, it's pretty fast.

It seems to be working quite well, though we hope to find out just how well with the iPhone server.

EDIT: I should mention that we're caching the items into a DB NPC to prevent tons of file I/O and slowdowns.

Switch · #21 12-24-2009, 03:33 AM

Quote:

Originally Posted by Immolate

It would be really handy if you could provide tips and examples on how to properly work with it, since all I know is the basic syntax.

Quote:

Originally Posted by SKJF91

Just simple queries, inserting, selecting etc.. - and I know how to use proper queries such as JOIN etc although i'm unsure if sqlite supports all of that like mysql does.

http://www.sqlite.org/lang.html

SKJF91 · #22 12-24-2009, 05:17 AM

Quote:

Originally Posted by Switch

http://www.sqlite.org/lang.html

And why are you sending this to me..?

Switch · #23 12-24-2009, 07:26 AM

Quote:

Originally Posted by SKJF91

And why are you sending this to me..?

Hmm...

Quote:

Originally Posted by SKJF91

although i'm unsure if sqlite supports all of that like mysql does.

Immolate · #24 12-24-2009, 01:28 PM

Quote:

Originally Posted by SKJF91

And why are you sending this to me..?

I'm thinking the same thing. I did say I knew the syntax but what I was asking for was tips on optimization from scripters who know how to use it properly.

SKJF91 · #25 12-24-2009, 06:49 PM

Quote:

Originally Posted by Immolate

I'm thinking the same thing. I did say I knew the syntax but what I was asking for was tips on optimization from scripters who know how to use it properly.

Well I see why he was sending me it, so I retract my statement

.

Immolate · #26 12-24-2009, 06:53 PM

Quote:

Originally Posted by SKJF91

Well I see why he was sending me it, so I retract my statement

.

I get the feeling he was trying to be a smart arse.

SKJF91 · #27 12-24-2009, 07:56 PM

Quote:

Originally Posted by Immolate

I get the feeling he was trying to be a smart arse.

Possibly.

coreys · #28 12-25-2009, 12:55 AM

Quote:

Originally Posted by Immolate

I get the feeling he was trying to be a smart arse.

No, he would have been if he send you this:
http://lmgtfy.com/?q=sqlite

cbk1994 · #29 12-25-2009, 01:03 AM

Quote:

Originally Posted by coreys

No, he would have been if he send you this:
http://lmgtfy.com/?q=sqlite

Aha, that's awesome.

cyan3 · #30 12-25-2009, 01:40 AM

Quote:

Originally Posted by coreys

No, he would have been if he send you this:
http://lmgtfy.com/?q=sqlite

I've been looking for that link for a while

thanks.

SKJF91 · #31 12-25-2009, 01:41 AM

Quote:

Originally Posted by coreys

No, he would have been if he send you this:
http://lmgtfy.com/?q=sqlite

rep+

View Poll Results: Best way to store item data
SQLite	5	55.56%
Files	2	22.22%
DBNPC	2	22.22%
Voters: 9. You may not vote on this poll