Online Databases

[??????]

Hey there, first off, wasn't sure if this was the right place, but I couldn't think of a better one, so yea...

Basically, I am wondering what you guys think would be required to store in online databases in order to create fun && functional online systems within rpg maker.

Example:

In order to have a database of Registered Users,so people have to physically log in to your game, one would be required to have a database to store a minimum of:
-> UserID, UserName, Password, LastLoginDate, Email?,
 

In order to make that database actually effective within an rpg maker game, so that your actor data was stored in another database you would probably need the following:

-> CharID, UserID, ActorName, FaceName, Charname, Gold, Inventory, Steps, Map Pos, lv, Exp, Hp, Mp, equip, skills, GuildID,
 

Then, what if you want other systems?

What are the things one would need to store on the databases?

Like, you could have everything stored within online databases...

Anyway, here are the databases I can think of along with some information each database would have to hold - obviously all databases would have to be read and the data sorted, but thats not really the point of this discussion....

#===============================================================================# -> Registered Users:  [ REQUIRED ]#====================##   --> Unique User ID,#   --> Username,#   --> Password,#   --> Date Created,#   --> Last Login Date,#   --> Registered Email,#   --> Warnings,#   --> Ban Flag,#   --> Unban Date,#   --> Party ID's,##===============## -> User Partys:  [ REQUIRED ]#===============##   --> Unique Party ID,#   --> Playtime,#   --> Gold,#   --> Steps,#   --> Map ID && Pos && Direction,#   --> Array Of Members (Unique Char ID's),#   --> Unique InvSet ID,#   --> Unique VarSet ID,#   --> Unique SwiSet ID,#   --> Unique Friendlist ID,  < [ ADDITIONAL ]#   --> GuildID,               < [ ADDITIONAL ]##===============## -> Party Chars:  [ REQUIRED ]#===============##   --> Unique Char ID,#   --> ActorID(rpgacedatabase),#   --> ActorName,#   --> ActorNickname,#   --> FaceName&&Index,#   --> Charname&&Index,#   --> Class ID,#   --> Level && Exp,#   --> Hp,#   --> Mp,#   --> Equip (array of weapon/armor ID's),#   --> Skills (array of skill ID's),#   --> Description,##=================## -> Inventory Set:  [ REQUIRED ]#=================##   --> Unique InventoryID,#   --> Items (array of item ID's),#   --> Weapons (array of weapon ID's),#   --> Armors (array of armor ID's),##================## -> Variable Set:  [ REQUIRED ]#================##   --> Unique Variable Set ID,#   --> Variables, (array of values (max size - 999. rpgacedatabase limit)##==============## -> Switch Set:  [ REQUIRED ]#==============##   --> Unique Switch Set ID,#   --> Switches, (array of values (max size - 999. rpgacedatabase limit)##===============================================================================# FriendLists:  [ ADDITIONAL ]#============##   --> Unique List ID,#   --> Array of friend's CharID's.##==============## Guild/Faction:  [ ADDITIONAL ]#==============## -> GuildID, UserID(leader), GuildName, Lv, Members, IconID,#   --> Unique Guild ID,#   --> Guild Name,#   --> Guild Level,#   --> Guild Members,#   --> Guild Icon,#   --> Guild Slogan,##=====## Mail:  [ ADDITIONAL ]#=====##   --> Mail ID? (not really required I think),#   --> To (Unique Char ID),#   --> From (Unique Char ID),#   --> Subject,#   --> Send Date,#   --> Read Flag,#   --> Expire Date,#   --> Message,#   --> Item,#   --> Gold,##======## Shops:  [ ADDITIONAL ]#======##   --> Shop ID,#   --> Owner (Unique Char/Guild ID),#   --> For Sale Flag && Price,#   --> Items For Sale && Prices,#   --> Items Wanted && Offer Prices,##========## Economy:  [ ADDITIONAL ]#========## (each item in game is 'manufactured' by a 'company', each time items are sold#  in game the 'stock price' of 'company' changes.)#   --> Unique Company ID,#   --> CompanyLogo ID(Icon ID),#   --> Stock Price,#   --> Items Manufactured (array of ID's),#   --> Weapons Manufactured (array of ID's),#   --> Armors Manufactured (array of ID's),#   --> Owner Name (?????),##===============================================================================# Excluded:#=========##   --> Events self switch information,#   --> ,##===============================================================================# Patching:#=========## Patching system can update Items, Weapons, Armors, Maps, Events, Animations,# Enemies && Troops, Additional User Scripts/settings (quests whatever) etc..##===============================================================================So yea, is there anything else that would need stored on external servers for 'average' online features to be brought to ace ?
Thoughts?

 

[Sarlecc]

You may want to store the graphics and audio files server side. Possibly most scripts too (except scripts like main or ones that handle the client). I'm guessing that saves will be server side? Well these are basically my ideas (think most mmo's/online games do this).

 

[Venka]

you'll probably have to store their location as well unless they always wake up at a set destination. I know you have your input script so I don't know if you plan on having a communication system within the game, which usually leads to a friends list. So if this is in planned somewhere down the line, then you'd probably have to store friends as well.

 

[??????]

You may want to store the graphics and audio files server side. Possibly most scripts too (except scripts like main or ones that handle the client). I'm guessing that saves will be server side? Well these are basically my ideas (think most mmo's/online games do this).

I was thinking of storing graphics etc on server, but that would lead to insane bandwidth usage over a small period of time, so its best to have those only downloaded at the initial 'install game / patch game' process, also, there are various ways to 'encrypt' imagery fairly easily.

But yea, all saved game data - or data that one would attempt to misuse in some way would be held on the server. For example, with player gold being held on ad database you can apply additional protection on your server code to avoid people using things like cheat engine effectively for their gold.

you'll probably have to store their location as well unless they always wake up at a set destination. I know you have your input script so I don't know if you plan on having a communication system within the game, which usually leads to a friends list. So if this is in planned somewhere down the line, then you'd probably have to store friends as well.

Ya, the map position would store the map id, the xy and their direction - naturally ^_^

Yes, a friend list is also something that would need to be added, good spotting. I will add what I can to the op on that ^_^

Edit:

Ok, I fixed up the OP to contain some friend list things..

Still not sure on the database structure, but i shall get to that..

Also, seeing as I'm linking it everywhere I can...



^_^

 

[Sarlecc]

I was thinking of storing graphics etc on server, but that would lead to insane bandwidth usage over a small period of time, so its best to have those only downloaded at the initial 'install game / patch game' process, also, there are various ways to 'encrypt' imagery fairly easily.

Yea I didn't think of the bandwidth issues.

Noticed you don't have enemy/troop data listed. Also may want to have a BanFlag for registered users in the event they are banned.

Edit: Just watched the vid^^. Can't wait to see if this works out.

 

[Bloodmorphed]

This is quite interesting. Can't wait to see what it's like when it is done. When I had gotten ebtter at scripting and stuff I was thinking about doing this as well. But your far more qualified for that then I. Lol

 

[phoenix_rossy]

A system whereby your party data is automatically uploaded, and you can fight other player's parties (controlled by AI, server side) PvP is something I would be VERY interested in.

Maybe even an item/actor 'trade' system to go along with that. Thoughts?

Where would the Database have to be stored? Any kind of cloud (ie Dropbox, Amazon S3)?

 

[Andar]

@phoenix_rossy:


An online-Database has no server-side functions, those would require a program for a server, not simply a database.


And no, while an online database isn't a full server program, it still needs its own server (usually an SQL-Server) and can't be stored in cloud services (or not unless you go through a lot of additional problems that would slow down access).

 

[Zeriab]

Amazon S3 is rather more about storing various blobs of data than structured data. For that Amazon RDS or Microsoft Azure SQL Database better suited. Cloud SQL services are those about. I bet there are several other companies offering similar SQL Database as a service like Rackspace.

If you do not absolutely need the elasticity (scale-up, scale-down) and high availability of such services expect them to be an expensive acquaintance.

Be mindful of other possible constructs as well.

 

[??????]

@Sarlecc - Ahh yea, a banflag is some good thinking ^_^

Also, the 'troops' arent an issue for me personally, as I will be using some kind of ABS (as i have more exp making abs's than turn based battles) for the end project.

the enemies though, yes, thats another good spot

@pheonix - Again, turn based isnt really something I enjoy messing with; however, if I was to write a decent enemy AI system then it would be easy enough to obtain 'x' persons party data and launch a simulated battle with them. Like, the battle would be controlled using AI (code within the editor) but all stats and skills and such would be that of other players party.

Could even go a step further and allow each player to set their battle style, get some kinda FF12 Gambits system going on (which i already had working for my old - old abs) and then the player can physically setup what will happen when they are challenged.

Then, could add some kinda system where by when beaten you lose gold to the other player - then get 'safeguarded' for a period of time (where you cant be rebattled)

I could keep going...

Anyway, I updated the OP again with some other things that I think would be best - such as putting each players inventory data into its own database table - this would be easier for me when I need to add unique equipments

Oh also - You can use dropbox and google drive (whatever) for certain things. Like returning some crap info from a .txt file or whatever, but you wouldn't be able to perform database queries that way.

Personally, I use ipage (recommended to me by Jesse@PVGames) as my web-host. They do everything i need for a fair price (at least for the first year). They allow me unlimited sql databases and I can easily upload files via ftp and whatever else I need. Obviously it is able to process php requests to my sql databases as well (cause thats how you get data from a server )

also - for offline testing I use Abyss web server, MySql Workbench6.2, Php5.something, and phpmyadmin (naturally).

All this is utilized via performing HTTP requests from within the rpg maker script editor. Your php searches databases and returns info, the ruby code (in the maker) then separates all the data as required and sets up the game.

tis actually fairly simple ^_^

 

[Engr. Adiktuzmiko]

I'm quite a super noob when it comes to networking, databases and such so this might be a dumb question but, can we for example just upload/update a file containing the classes used by RM instead of saving the things like charID and such in the database?

 

[Shaz]

A file like RM's has to be broken up into records, and the records broken up into fields. A database already does this for you, making querying such data much faster and a server-side responsibility, rather than something done by the client.

 

[phoenix_rossy]

@pheonix - Again, turn based isnt really something I enjoy messing with; however, if I was to write a decent enemy AI system then it would be easy enough to obtain 'x' persons party data and launch a simulated battle with them. Like, the battle would be controlled using AI (code within the editor) but all stats and skills and such would be that of other players party.

Could even go a step further and allow each player to set their battle style, get some kinda FF12 Gambits system going on (which i already had working for my old - old abs) and then the player can physically setup what will happen when they are challenged.

Then, could add some kinda system where by when beaten you lose gold to the other player - then get 'safeguarded' for a period of time (where you cant be rebattled)

I could keep going...

Anyway, I updated the OP again with some other things that I think would be best - such as putting each players inventory data into its own database table - this would be easier for me when I need to add unique equipments

Oh also - You can use dropbox and google drive (whatever) for certain things. Like returning some crap info from a .txt file or whatever, but you wouldn't be able to perform database queries that way.

Personally, I use ipage (recommended to me by Jesse@PVGames) as my web-host. They do everything i need for a fair price (at least for the first year). They allow me unlimited sql databases and I can easily upload files via ftp and whatever else I need. Obviously it is able to process php requests to my sql databases as well (cause thats how you get data from a server )

also - for offline testing I use Abyss web server, MySql Workbench6.2, Php5.something, and phpmyadmin (naturally).

All this is utilized via performing HTTP requests from within the rpg maker script editor. Your php searches databases and returns info, the ruby code (in the maker) then separates all the data as required and sets up the game.

tis actually fairly simple ^_^

Actually, I meant literally just downloading the data to your own game. Your team uploads to the server, and you download the data of another player's team. The engine then uses a custom script to place those values into database actors (I think Yanfly has someone along these lines - you can fight a mirror of your own team setup), which we can then fight through the engine. I'd have thought this relatively simple for someone with your skills

 

[Andar]

@Phoenix:


That way, you would be fighting your computer while it used the other player's data, but you would not be fighting the other player himself/herself as the other players would have no input on your fight.


That is easy, but it's not what most players would imagine as "fighting other player's parties".

 

[??????]

lol yea, thats actually far too simple..

I mean, that would be a case of simply holding a few strings on a single database for each party.

Each string would contain actor data - such as equips etc.

Then, the strings are put together to form a party and voila - tis done.

but yea, as Andar states, not really 'fighting other players' - more fighting a clone of their setup if anything.

And again, a Gambit like system (where the player can customize how their party autobattles) would make a huge difference.

I'm kinda more wanting to create a whole online environment (without other players being visible and walking around). so when you log in, you select your character (or make a new) and then begin your games story. Its would have the complete feel of an mmo - just with a few limitations (at least for now). You wil have access to various information (highscores whatever) from other players and will be able to send messages and communicate with them in various ways.

Further down the line I would like to make it possible to even go on a 'two player quest' (or something) where you and friend begin a quest together (and are able to see each other in game)

But for now, its just the basic online features. I already have it setup so that you can login and the main player etc changes. Having a little trouble getting multiple characters per user account, but when I have more free time ill get to that ^_^

Dropping a link casually...





Uhh, also...

I'm not sure on the whole 'variables and switches' thing. Like, should I control all variables on a single database (meaning all players have the same variables and switches active), or should I allow the game to have control of them? (meaning they would all be loaded at startup from the users account - each accounts variables can differ)

Kinda leaning towards a mixture. Like, 100 variables / switches are server controlled, the rest are character account based. Suppose I could just as easy make 100 new variables in some module and use those for the server controlled variables..
like...

module NewVariz  class << self    def [](id)      data[id]    end    def []=(id,val)      data[id] = val    end    def data      @data ||= []    end  endendAnyway, thoughts on that?

 

[phoenix_rossy]

@Phoenix:

That way, you would be fighting your computer while it used the other player's data, but you would not be fighting the other player himself/herself as the other players would have no input on your fight.

That is easy, but it's not what most players would imagine as "fighting other player's parties".

Perhaps so, but it's a nifty feature nonetheless, for a game with as much class customisation as I'm currently developing. It would just be nice to see what else other people are cooking up!

lol yea, thats actually far too simple..

I mean, that would be a case of simply holding a few strings on a single database for each party.

Each string would contain actor data - such as equips etc.

Then, the strings are put together to form a party and voila - tis done.

but yea, as Andar states, not really 'fighting other players' - more fighting a clone of their setup if anything.

And again, a Gambit like system (where the player can customize how their party autobattles) would make a huge difference.

I'm kinda more wanting to create a whole online environment (without other players being visible and walking around). so when you log in, you select your character (or make a new) and then begin your games story. Its would have the complete feel of an mmo - just with a few limitations (at least for now). You wil have access to various information (highscores whatever) from other players and will be able to send messages and communicate with them in various ways.

Further down the line I would like to make it possible to even go on a 'two player quest' (or something) where you and friend begin a quest together (and are able to see each other in game)

But for now, its just the basic online features. I already have it setup so that you can login and the main player etc changes. Having a little trouble getting multiple characters per user account, but when I have more free time ill get to that ^_^

Dropping a link casually...





Uhh, also...

I'm not sure on the whole 'variables and switches' thing. Like, should I control all variables on a single database (meaning all players have the same variables and switches active), or should I allow the game to have control of them? (meaning they would all be loaded at startup from the users account - each accounts variables can differ)

Kinda leaning towards a mixture. Like, 100 variables / switches are server controlled, the rest are character account based. Suppose I could just as easy make 100 new variables in some module and use those for the server controlled variables..

like...

module NewVariz  class << self    def [](id)      data[id]    end    def []=(id,val)      data[id] = val    end    def data      @data ||= []    end  endendAnyway, thoughts on that?

Variables/switches should probably be stored server-side, but then you have the possibility of gaining popularity and ending up with millions/billions of active variables/booleans 0.o

Anything you can create that has online features gets a thumbs-up from me. And depending on the license fee, I'd be interested on a commercial basis.

It's great that people such as yourself are trying to make the RM engine even more diverse than it already is. It helps me to stop feeling like the natural progression is to move on from RM into bigger/better/faster/3D engines - we should be able to sit in our comfort zone for as long as we damn well please

 

[Sarlecc]

@Dekita For multiple characters per account could you possibly use a hash to store multiple saves into one file for that account? Then display it onto like a save menu based on which id in the hash the save was stored in after the player logs in. If you could do this it would help keep the amount of files down quite a bit (instead of say four files for each player, you would reduce it to one).

I agree that you probably want some variables/switches for all players, as well as some that are character/account unique. This would allow you to easily do things like controlled events for all players vs just select players.

 

[??????]

Perhaps so, but it's a nifty feature nonetheless, for a game with as much class customisation as I'm currently developing. It would just be nice to see what else other people are cooking up!

Variables/switches should probably be stored server-side, but then you have the possibility of gaining popularity and ending up with millions/billions of active variables/booleans 0.o

Anything you can create that has online features gets a thumbs-up from me. And depending on the license fee, I'd be interested on a commercial basis.

It's great that people such as yourself are trying to make the RM engine even more diverse than it already is. It helps me to stop feeling like the natural progression is to move on from RM into bigger/better/faster/3D engines - we should be able to sit in our comfort zone for as long as we damn well please

Yea, I guess it would be a nice 'calm' feature that could potentially bring a fair amount into a game.

Realistically, the only way I would end up with so many bools floating around would be if i had a number of players equal to bool amount / 1000. So 1 million bools equals 1000 players - which is fine by me ^_^

And also, each database will likely get somewhat large whatever I do.

For now, none of it will be available (at all). When I feel I've done enough I will be uploading an 'encrypted' demo to allow you guys to play around with it and gimme feedback. After that, the systems will be released (for non commercial ofc) and I would be wiling to hear peoples plea's for obtaining the systems for their commercial projects ^_^

And yea, quite a few times I have felt like leaving RM for something more suited to be abilities. Hell, I could (in theory) write my own game engine at this stage (would likely require a **** load of google reading ) but yea, I agree that we need folk who are actually enhancing the engine beyond the default capabilities. Whether that be in the form of complex script && dll packages or in simple 10 lines of code snippets. It all helps

@Sarlecc:

That is how data is stored within the default system, but not how i will be storing it on my database. It is possible; however, it would lead to excessive data downloads each time the user wanted to change a character. - For example, a compressed and encrypted save file is around 16kb in size. if each user profile had say 3 files, thats 48kb of data being sent (to each user) each time they begin the char change screen. Sending an actual php /mysql query reply the data value varies depending on the length of returned data, so for example, the data might be this:

"w1,a1,a2,a3,a4,a5"which is a string containing umm 18bytes? (could be off there, but i think I'm right), which is a huge difference from 48KB (which is roughly 2666% more (again - I think) )

so yea, as you can see its much more effective to simply return the required data, rather than compressing every single game object, creating a new file on my server (which takes physical memory up) storing said data in the file and then sending that file back to the game each time its required - or sending new data to that file - literally, every single tie the whole file would require to be rewritten, rather than just being able to (for example), update the players gold. (which again, would be a tiny size of data)

And yea, that was my thinking of having both server controlled and account unique vars/switches. Cause then you could easily trigger some global event by changing a switch value on your server.

 

[Sarlecc]

That is how data is stored within the default system

Default saves the data into a new hash for each file (unless I am missing something). I was asking about storing the data of each character into a hash then storing that into a single file (could you save just the the parts in the hash that changed?).

Though your explanation makes a lot of sense why the above would be a bad idea for server use. (probably would be bad for single player use to in the event the file with the character saves got deleted now that I think of it). Thank you for explaining these things (I am still learning lol).

Edit at below: Thanks for the even more expanded version of the explanation.

 

[??????]

Yea, but its not much different because each 'character' would essentially have to replicate all the information a save file would hold - such as items, equipment, map location and so forth. (at least for a full game experience). For something like a simple system that transfers player B's party data to player A's game client you would only require a small database, say one table containing an id value and 4 text strings.

The strings could look somewhat like thiis...

"1;w1,a1,a2,a3,a4;1,2,3,4,5;589098;4;"In this example, it could create actor id 1, equipped with weapon 1 and armor 1,2,3 & 4. It then learns skills 1 2 3 4 and 5, gains 589098 exp and changes to class id 4. 

Obviously each string recreates an actor and in turn, a party is generated. Then its just a case of being able to battle a party replica.

Also, as you can see, this stores a crap load of information in /VERY\ little space (and if you had thoroughly planned things you could probably condense it further).

When saving actual game objects, you must save every single thing. so all variables and random crap from other peoples scripts would be included in the files. Yea, that may be good for some situations, but its likely the bandwidth usage again would cause issues. And then you also have the problem of where all these save files are going to be physically stored on your server.

Again, lets use the previous example of 1000 users (when we had the hypothetical 1million booleans floating in the database). Each user has 3 files, each file around 5kb (lets say we manage to create a new save technique optimized for the situation) so then we have

1000(users) x 3(chars per user) x 5kb(size) = 14.8MB (ish).Compared to the string from above (which I believe is 36Bytes in size)

1000(users) x 3(chars per user) x 36Byte = 106.7KB (ish).so yea, overall that would reduce the database size by around 93%. Which means 93% server bandwidth was just saved, 93% of download time or startup time or whatever you cal it was just removed, and most importantly, your workload has been reduced 93%

Furthermore, cause you wouldnt have all the random files and **** floating around. it would be much easier for you to alter users accounts (for whatever reason you may have).

I remember I had to get a GM to login to my account to fix a bug with my demon skills

Was all legit (I logged an alt char and watched them, they logged my toon but it had their name with a GM logo, all my equip and such though)

Uhh yea...

\rant over

Edit:

Updated the op with a new list of required and additional things that a database would have to hold.