RequestSQL Ideas

[Admins]

The support for SQL has been added a few days ago, restart your npcserver for using requestsql() if you have not done it already.

If you have already tried the SQL things, may be post some ideas about what you plan to do with it

[xXziroXx]

I've converted the entire player data system on Maloria from using .ini files to SQLite.

Quite easy to query all items from an account, and a lot faster and less demanding then constant loading of files.

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

In the items table, I have the variables account, entryIndex, item_id, item_data where account shows who owns the item (d'uh), entryIndex is an auto increasing variable for registry index, item_id the actual item's id (more on that here) and finally, item_data is an array containing all the data for the particular item.


However, when I finished the code, I got the brilliant idea to loop through 3500+ files and convert them to SQLite, instead of dealing with each individual player when he/she logs on, and got this - think it's a new record!

PHP Code:

Top 10 of npcs taken most CPU time:
1. 803.54333169 % SQLDebug (in level onlinestartlocal.nw at pos (30.5, 30)) 


[Crow]

Originally Posted by xXziroXx

However, when I finished the code, I got the brilliant idea to loop through 3500+ files and convert them to SQLite, instead of dealing with each individual player when he/she logs on, and got this - think it's a new record!

PHP Code:

Top 10 of npcs taken most CPU time:
1. 803.54333169 % SQLDebug (in level onlinestartlocal.nw at pos (30.5, 30)) 


Sure as hell is. Most I've seen was 216 % from a /stats query on Zodiac once.

[Inverness]

I'm considering redoing the MUD on Valikorlia to use SQL and stuff, but I don't want to use SQL unless I'm willing to make a nice GUI for managing the system since you can no longer edit by text file, but I think I will.

[cbk1994]

Originally Posted by Crow

Sure as hell is. Most I've seen was 216[percent] from a /stats query on Zodiac once.

Wow, I thought I hit a record yesterday when running a level scanner that got to 112[percent].

[WanDaMan]

oh coool

What're the SQL functions, and is it really better for storing items that way? lol

[fowlplay4]

Me and Chompy are currently in the planning process of a new MUD for Zodiac, we will definitely consider using it for data storage.

If all the important information is consolidated into the SQL Database should make it ALOT easier on the server to do character backups and so forth.

[xXziroXx]

Originally Posted by fowlplay4

If all the important information is consolidated into the SQL Database should make it ALOT easier on the server to do character backups and so forth.

That's what made me rework the system on Maloria to use SQLite

[napo_p2p]

Ideally, I'd like to make some kind of phpMyAdmin-like administration tool. I just have to find the time and inspiration...

[Tigairius]

I plan to change a lot of GK's logging system to use SQLite for easy log management (especially for trade tables, etc). Era Dev is also using this idea to bring logs into SQLite as well.

[napo_p2p]

Originally Posted by Tigairius

I plan to change a lot of GK's logging system to use SQLite for easy log management (especially for trade tables, etc). Era Dev is also using this idea to bring logs into SQLite as well.

That isn't a bad idea at all. I'll probably end up doing the same thing .

[cbk1994]

Originally Posted by napo_p2p

That isn't a bad idea at all. I'll probably end up doing the same thing .

It's pretty cool. We can make queries like this:

NPC Code:

---

SELECT * FROM logs_jail WHERE account = 'cbk1994'

---

or

NPC Code:

---

SELECT * FROM logs_jail WHERE jailer = 'Tigairius'

---

or even

NPC Code:

---

SELECT * FROM logs_jail WHERE time > 60

---

without having countless files in logs/*

[firefighter]

We on Zenox are using the SQLite database for our item system now. Maybe I will rescript some other systems to use SQLite. Its a great idea I think because its much faster and cleaner than using flat files (normal DB NPC's).

A good feature would be if I can convert defined row results directly into an array without using a for loop. For example:

PHP Code:

public function GetPlayerItems(placc) {
  temp.items = {};
  temp.req = GetSQL("SELECT ItemDB_Items.name AS itemname FROM ItemDB_Items INNER JOIN ItemDB_PlayerItems ON ItemDB_Items.ID=ItemDB_PlayerItems.item WHERE ItemDB_PlayerItems.player=(SELECT ID FROM Players WHERE account LIKE '"@SQLEscape(placc)@"')",true);
  if (temp.req!=null && temp.req.error=="") {
    for (temp.row : temp.req.rows)
      temp.items.add(temp.row.itemname);
  }
  return temp.items;
} 


Okay its not a big problem with a for loop but I think it can be solved more optimized