Any thoughts on creating a standard database schema for runescape private servers? Along with standard stored procedure definitions?
Creating a Standard Database Schema
well my gut reaction is a table of username, pid so you can cross reference other tables such as stats, inventory, equipment, and the like.
select pid from users where username = 'aname'
select attack from stats where pid = apidThat’s the basic idea:
(something throw together real quick like)
Would anyone like to see this become a community project?
I don’t think it’s enough work to call it a community project 
Back when I was working on champions realm, we already had this sort of thing set up. It looked pretty much exactly like this. Except we didn’t have ‘skilllib’ It integrated very well into our website
@Davidi2, I’m guessing the end schema will have something along the lines of 10-15 tables fully normalized. (Thats when you add, things such as, chat logs, trade logs, world definitions, shop definitions, npc definitions, item definitions, etc etc)
I don’t think adding npc, item, and world definitions is really necessary. The server is limited by what content the client already has, seeing as the server can only manipulate that (with the exception of custom regions for instanced realms). The item, world, and npc definitions should be loaded from the cache files and all general, persistent content should be stored in the database.
Think about it from a production point of view. If you were to ever update your items, npcs, and/or world objects or landscape files, you could do it in 1 pass when repacking the cache and just provide that to the server (seeing as the server handles updating, as well), or you would need to parse those definitions and insert them into the database, adding additional load and requirement.
As far as an infrastructure point of view goes, whether or not Jagex uses databases per server is in question. I’m fairly certain they don’t use database servers, but I wouldn’t be surprised if they used SQL Lite or some equivalent. As far as the player’s profiles are concerned, those are stored within their few loginservers on oracle database servers. That essentially means that none of those content fields you suggested would be stored with the player, only the player’s own information. When it comes to things like shops and where an NPC is in the world, those don’t need to be persisted anyway and can be stored directly in server memory (or, as I referenced before, in a SQL Lite database).
There are plenty of definitions that the client doesn’t have
Give me some examples. All static data is known by the client. People recreate their “config” files based on that static data. In reality, shops, default item drops, and npc spawns are the main things the server needs to know, which are all configuration based.
[quote=“Taharok, post:8, topic:454101”][quote author=Davidi2 link=topic=556975.msg4077174#msg4077174 date=1344961909]
There are plenty of definitions that the client doesn’t have
[/quote]
Give me some examples. All static data is known by the client. People recreate their “config” files based on that static data. In reality, shops, default item drops, and npc spawns are the main things the server needs to know, which are all configuration based.[/quote]Really? I never really looked at the cache itself but I remember some things not working ‘out of the box’ when I was writing my server, like examine texts and item stats. Note it was a 508, I’m not sure if there is a difference in that but yea. Mind you I still think having definitions in database format is worthwhile (even if not for the server), it opens up your opportunities for web and other development greatly. Think: wiki, web trading with info, etc
Yeah, examine text is in the 317 cache for example but not in 508.
There’s a MySQL Workbench file in Elysium (an RSC server developed with a focus on concurrency), found here: https://github.com/Lothy/Elysium.
The file is elysium.mwb.
Anyway, the schema is actually pretty big iirc because it does contain definition information. Realistically, a lot of it could be left as XML, but items/npcs/objects definitely need to be in the database so that you can record statistical information.
[quote=“Lothy, post:11, topic:454101”]There’s a MySQL Workbench file in Elysium (an RSC server developed with a focus on concurrency), found here: https://github.com/Lothy/Elysium.
The file is elysium.mwb.
Anyway, the schema is actually pretty big iirc because it does contain definition information. Realistically, a lot of it could be left as XML, but items/npcs/objects definitely need to be in the database so that you can record statistical information.[/quote]
Only referentially. All the extra data doesn’t really need to be there. Like I said earlier, it doesn’t fit into the general jagex-styled server infrastructure. You would need a shared database system for players and individual database systems for each server, which seems messy to me.
What is this ‘jagex-styled server infrastructure’ that you speak of?
Anyway, the reason for putting things like items/npcs/objects/etc in is so that you can record information against them. For example, you could then have proper item transfer auditing in your system.
For example, each item/stack could have a UID, which can then be traced through dropping/trades/etc. When you merge two stacks together, you create a new UID for the resulting stack.
The result? Item transfer is traceable. This means that theft is no longer a problem, and trading between accounts can be detected (if this is against the rules).
Alternatively, you can track stuff for competitive purposes – such as number of npc X that was killed – if you so desire.
tl;dr: There’s so much cool stuff you can do with a properly developed database, so why cop out and do it half-arsed?
[hr]
Somewhat unrelated, you’ll never be able to have a centralised repository of anything for this community in terms of data because the network of trust (i.e., anyone would be using it) is simply too large. There would be oversights in the design, and the end result would likely be a system that could be breached/poisoned by a malicious user.
“There’s so much cool stuff you can do with a properly developed database, so why cop out and do it half-arsed?”
EXACTLY!
[quote=“Lothy, post:13, topic:454101”]What is this ‘jagex-styled server infrastructure’ that you speak of?
Anyway, the reason for putting things like items/npcs/objects/etc in is so that you can record information against them. For example, you could then have proper item transfer auditing in your system.
For example, each item/stack could have a UID, which can then be traced through dropping/trades/etc. When you merge two stacks together, you create a new UID for the resulting stack.
The result? Item transfer is traceable. This means that theft is no longer a problem, and trading between accounts can be detected (if this is against the rules).
Alternatively, you can track stuff for competitive purposes – such as number of npc X that was killed – if you so desire.
tl;dr: There’s so much cool stuff you can do with a properly developed database, so why cop out and do it half-arsed?
[hr]
Somewhat unrelated, you’ll never be able to have a centralised repository of anything for this community in terms of data because the network of trust (i.e., anyone would be using it) is simply too large. There would be oversights in the design, and the end result would likely be a system that could be breached/poisoned by a malicious user.[/quote]
Well I’m not advocating that this is the sole way to setup a multi-server infrastructure for RSPS or anything, I’m just aware of Jagex doing it this way. You send your login authentication packet to the game server, which then resends it to a loginserver to verify whether you’re already logged in and whether your login is valid. The loginserver is then responsible for maintaining your logged state. When you logout of the gameserver, the gameserver must send the logout packet to the login server (many times this doesn’t happen, which leads to session issues in currently live rs). There’s also the friends/chat servers which maintain communication between players. I always assumed that these were one in the same with the loginservers, but some of the things jagex have said over the last few months make me think they aren’t.
I have to agree with Davidi2 on the “opportunities” comment. However, wouldn’t it be logical to grab rather than to store if you were to use this? Mind you, it’s been a while since I’ve been around. I could be talking completely out of my ass, but using an outside source to store data doesn’t seem to be necessary if it can be stored within it’s own environment. (excuse the somewhat-ambiguous terminology)
However, I’d promote grabbing things for highscores, or any int variable for that matter to display on a more visually appealing canvas than an rs client. Just a stupid thought from the stupid kid that dropped out of the scene forever.