Graal Forums - RequestSQL Ideas

Graal Forums (https://forums.graalonline.com/forums/index.php)

- NPC Scripting (https://forums.graalonline.com/forums/forumdisplay.php?f=8)

- - RequestSQL Ideas (https://forums.graalonline.com/forums/showthread.php?t=85164)

Admins

04-15-2009 11:34 PM

RequestSQL Ideas

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

04-18-2009 07:08 AM

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	04-18-2009 11:15 AM

Quote:

Originally Posted by xXziroXx (Post 1484768)

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

04-18-2009 06:00 PM

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

04-18-2009 06:06 PM

Quote:

Originally Posted by Crow (Post 1484779)

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

04-18-2009 06:43 PM

oh coool

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

fowlplay4

04-19-2009 04:39 AM

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

04-19-2009 03:17 PM

Quote:

Originally Posted by fowlplay4 (Post 1485047)

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

04-20-2009 06:24 AM

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

Tigairius

05-16-2009 10:08 PM

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

napo_p2p

05-16-2009 10:24 PM

Quote:

Originally Posted by Tigairius (Post 1491844)

I plan to change a lot of GK's logging system to use SQLite for easy log management (especially for trade tables, etc). :D 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 :p.

cbk1994

05-17-2009 12:03 AM

Quote:

Originally Posted by napo_p2p (Post 1491845)

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

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

NPC Code:

SELECT * FROM logs_jail WHERE account = 'cbk1994'

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

08-18-2009 08:48 PM

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

All times are GMT +2. The time now is 08:37 PM.