SQL Item System
 

So, I've designed an SQL item system, the only problem that I don't like is the loading of the data and the time it's taking to request things from it. Is there any possible way to speed this up? Or am I doing something wrong... thanks

How -are- you doing it?

Stored items in an SQL database, getting the item data from the database by an ID of the item. On login/item adding it's copying the values from the ID and storing them as ''player.item_id.var''.
I was going to create a table to contain players accounts and such...

thanks

still not a very thorough explanation.

How do you determine what items to look up for an individual player?

Oh- just as a flag
flag = {id, amount}

The SQL database contains all the data for the items, while the player simply holds the ID's for the items they own. He looks up the ID for the item in the database and loads them onto the player.

Are you using transactions (instead of just using single commands)?
Inverness mentioned this to me yesterday; I don't know for sure that Graal even supports transactions, so let me know if I'm wrong.

Do you have the id set as the primary key?

He's actually using alphanumeric ID's for actual reference(easier to add "swordtest1" rather than "231" or whatever numeric ID is assigned).

If you're doing like

NPC Code:

---

SELECT * FROM items WHERE itemid=swordtest1

---

Have you set that 'itemid' is UNIQUE when creating the table?

NPC Code:

---

CREATE TABLE items(itemid TEXT UNIQUE DEFAULT '')

---

That would make it easier to index and faster to recall.

Here's how I'm doing it on Maloria.


Table creation...

NPC Code:

---

CREATE TABLE items (entry_index INTEGER PRIMARY KEY AUTOINCREMENT, account VARCHAR NOT NULL DEFAULT 'ACCOUNT_ERROR', item_id VARCHAR NOT NULL DEFAULT 'ID_ERROR', item_data VARCHAR NOT NULL DEFAULT 'DATA_ERROR')

---

Loading of items...

NPC Code:

---

SELECT * FROM items WHERE account = '" @ player.account @ "'"

---

Then I just loop through the query and add each item to player.items which is a TStaticVar that gets cleared everytime the player logs out.

I also automatically save all the players current items to the SQLite database upon onPlayerLogout().

Player should not have ids of items he owns, rather the items should have the account name of the player they belong to. The items should also be indexed by their account name.
#1. Why are you using AUTOINCREMENT?
#2. Do you know that the INTEGER PRIMARY KEY column is an alias for the built-in rowid column?
#3. VARCHAR is not a type in SQLite, it is just changed to TEXT, I suggest using that.
#4. If you only update the SQL when the player logs out then you're not using SQL correctly.

Aha- I see! So only on login/logout, I see the benefits to that actually! Thank you

Just wondering, I see you're replicating data in your database (each item having it's own item data- I don't plan on using this) so I think I'll stick with storing the items in the strings, thanks again

For speeding things up you can create an index (create index) on certain columns, that can make queries 100-1000 times faster.

Yeah- they're indexed. Thanks

Where would you recommend to save items @ Stefan. (the flags)