WCU Database Design & Documentation

Discuss the Wing Commander Series and find the latest information on the Wing Commander Universe privateer mod as well as the standalone mod Wasteland Incident project.
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

I think that the other issue is the expandability, and the ability for outside developers to make changes without having to muck in the document. I know this in itself has lead to numerous typographic errors that made everything from having a planet vanish to killing VS in general.

On expandability side of things just what I have in the DB right now would hit the 10MB barrier you are talking about if I converted it all into XML. I am trying to provide a foundation that is not only powerfully, but relatively easy to implement. Most people, your self probably included, think that the entire WCU will be a flat time line universe. (everything is there, nothing changes). However I solved this with one simple idea “time.â€
Last edited by mkruer on Wed Jul 20, 2005 11:05 pm, edited 1 time in total.
I know you believe you understand what you think I said.
But I am not sure you realize that what you heard is not what I meant.

Wing Commander Universe Forum | Wiki
Wing Commander: The Wasteland Incident
The Phantasm
Explorer
Explorer
Posts: 10
Joined: Wed Jul 20, 2005 5:23 pm

Post by The Phantasm »

Actually, I'd say that the ideal solution would be to have used this to start with (not that I expect it to actually work outside of the Apple environment). Flip one switch and the guts go from XML (which is much much easier to debug in realtime) to SQLLite.

http://developer.apple.com/documentatio ... /CoreData/
index.html

I'm not entirely sure why people love XML either, but a database is not as straightforward as you seem to be implying.

If you have a ship with 20 turrents of type x, I'm going to assume that you'll setup a ship table with some id, and you'll setup a line table of turrets which will have 20 entries with a foreign key pointing to that first ship. That would leave you with a whole lot of redundant entries as well.
Or, if in the line item table, you have a field that holds the number of that type of turret. That's fine too, and it reduces us to a ship record and a single turret line item. 2 records, no biggie.

What if your ship has 10 turrets of type x, 3 of type y, and 2 of type z?
You would setup a line item table that has 3 records, thus bringing you
to 4 records for this single ship. Again, you're going to have a boatload of records.

Alternatively, you could have a single field in the ship table that has a string formatted like: X: 10, Y: 3, Z: 2 for the turrets. That would reduce your total count to 1 record per ship, which really is a lot better, especially since it means you won't need to join against a turret table.
Also, in memory, it is more likely in this case that you will have a ship that has a list of turrets, but those turrets need not be full objects (otherwise, if you're dealing with 20 turrets per ship, you're going to have a whole lot of memory usage for that, and the more memory you eat up, the more you thrash your poor cache, which will only further reduce performance).

So, my point is that databases are not so hot at lists. They can do them, no problem, but that means a lot of line tables, which means a lot of records, a lot of joins, and a lot of inefficiency. How are you going to load all the ships and turrets out of a database? Do you load the table of ships and then grab for all the turrets later? Do you load the table of ships already joined to the turret table?

That's a whole lot of decisions to have to make that will have an impact down the line on the performance of the system.

If the whole point of changing away from XML is speed, I don't believe that just hopping into the database world is going to solve that problem.

And not all SQL is created equal. I personally use T-SQL at work, and I know full well that it won't run under MySQL or PostgreSQL or Oracle. Databases aren't quite as interchangable as you make them sound like.

As for building a custom data storage system from scratch, that's not exactly a difficult or daunting task. But, if you want suggestions for
alternatives, and you have a large number of people unfamiliar with the
intricacies of SQL and working with a database (SQL is programming in set theory. Set theory is not the same method of thinking as OO or functional programming.)

But give this a look-see. It's a good project.
http://www.db4o.com/


And again, I don't like XML, but I'm trying to understand what the problem is. mkruer said that the problem is that the XML loads too slowly. If the problem is performance, then the simplest solution is to replace the XML parser and leave the existing data alone.

This is the kind of decision that can kill a project like this. There seems to be a push to completely redesign a critical, working subsystem because of the performance of the subsystem. The system clearly needs to be refactored if it takes 5+ minutes to load the data. Does the system need to be completely scrapped and completely redesigned? I don't know. Is the system non-functional and/or does the system not do what is intended? It seems that the system works just fine, it's just slow. Ok, so find the critical path (the XML loader), rewrite it, and be done with it. Then move on to things that will make the game more interesting for you and for other people. Don't bog the project down in a month or two of flux just to rebuild the storage system.

Any changes of this scale must be evaluated based on their value. The value of moving to SQL right now is unknown. No one can say unequivocably that moving to a SQL database run through SQLLite or MySQL is going to fix the speed issue. Also, it's going to destabilize the entire program for quite awhile. A little storage issue can hide for a long time before you see it.

I think that everyone's time should be spent on better things than this. Someone should rebuild the XML parser to make it quick. File parsing is easy. I'll do it if no one else wants to. There's already an existing work-around for the speed issue (comma seperated value files), so that's not slowing anyone down.

So let's take the month or two and apply it to something that's an actual feature that people will see.
The Phantasm
Explorer
Explorer
Posts: 10
Joined: Wed Jul 20, 2005 5:23 pm

Post by The Phantasm »

Why why why?

mkruer:
Alright. You can build this gigantic database system designed for how you personally expect this project to be in 5 years. You can spend goodness knows how long building it, but until the project needs it and can use it, it's just going to be excess code sitting around waiting to be used. You'll be highly entertained writing it, but how does it really contribute?


> Even with all the headaches the pros still outweigh the cons in the end, and in the end this is not a “lets do it tomorrow” plan, its and “lest prepare for what will happen 5-10yrs down the line” plan.

Where precisely will this project be 5-10 years down the line? Will you really still be working on it? I doubt it.

This project needs some actual management. It needs a group of programmers willing to submit designs before they start coding. If there's a question of feasibility, sure, build a prototype and find out if something can be done. Don't run off and build 10 meg of data in a database and then just hack it into the system. That isn't helping this project.

Does everyone have the same game plan? I don't see a game plan at all. What comes next after this storage system rebuild? What systems exist, and what systems need to be built? And does anyone write design docs at all, or do people just post cryptic comments?
smbarbour
Fearless Venturer
Fearless Venturer
Posts: 610
Joined: Wed Mar 23, 2005 6:42 pm
Location: Northern Illinois

Post by smbarbour »

T-SQL is a language, not a database. That's actually kind of funny seeing as how I use T-SQL to access Oracle (as well as MSSQL) on a daily basis. The only difference is the proprietary functions that the different databases use. If we avoid those and stick to the basics, there shouldn't be any problem.

We can't just have a field that holds the number of the type of turret. They are individually placed subunits of the main unit (which also have a subunit of their own to allow for proper aiming).

XML is a cow, and no matter how fast you are able to milk the cow, there is a limit to how fast the milk will come out.

The database files are not interchangeable, but as long as there is support for ODBC, the data is.

The fact remains that we are at or near the limit of what can be accomplished using XML files.
I've stopped playing. I'm waiting for a new release.

I've kicked the MMO habit for now, but if I maintain enough money for an EVE-Online subscription, I'll be gone again.
The Phantasm
Explorer
Explorer
Posts: 10
Joined: Wed Jul 20, 2005 5:23 pm

Post by The Phantasm »

smbarbour:

How did you come to that conclusion? Where's the data to back that claim up? If I'm wrong, show me.

(as a side note, T-SQL is a superset of SQL. There are some commands
that are T-SQL specific (updating is different in T-SQL) that would not
scale over to MySQL)
smbarbour
Fearless Venturer
Fearless Venturer
Posts: 610
Joined: Wed Mar 23, 2005 6:42 pm
Location: Northern Illinois

Post by smbarbour »

Can you cite an example that shows that MySQL does not support T-SQL? Everything looks pretty standard to me.
I've stopped playing. I'm waiting for a new release.

I've kicked the MMO habit for now, but if I maintain enough money for an EVE-Online subscription, I'll be gone again.
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

The Phantasm wrote: Alright. You can build this gigantic database system designed for how you personally expect this project to be in 5 years. You can spend goodness knows how long building it, but until the project needs it and can use it, it's just going to be excess code sitting around waiting to be used. You'll be highly entertained writing it, but how does it really contribute?


A lot of people some to think so other wise we would not be having this conversation. On top of this I made this request back 3yrs ago, and the response was good idea IF we need it. Well apparently the IF has become NOW!
The Phantasm wrote:
Matt Kruer wrote: Even with all the headaches the pros still outweigh the cons in the end, and in the end this is not a “lets do it tomorrowâ€
I know you believe you understand what you think I said.
But I am not sure you realize that what you heard is not what I meant.

Wing Commander Universe Forum | Wiki
Wing Commander: The Wasteland Incident
smbarbour
Fearless Venturer
Fearless Venturer
Posts: 610
Joined: Wed Mar 23, 2005 6:42 pm
Location: Northern Illinois

Post by smbarbour »

mkruer wrote:
The Phantasm wrote:And does anyone write design docs at all, or do people just post cryptic comments?
mainly cryptic comments.
It seems to keep people who don't understand the project from posting, but occasionally we see the odd individuals who's sole purpose is to question decisions.

Other systems are being worked on by various other people.

The main reasons for this discussion are 1) The database is becoming larger than XML can handle properly; and 2) Multiplayer is a near-term goal for many of the developers. XML will not be able to handle a multiplayer environment, especially if an when it becomes MMO.
I've stopped playing. I'm waiting for a new release.

I've kicked the MMO habit for now, but if I maintain enough money for an EVE-Online subscription, I'll be gone again.
spiritplumber
Developer
Developer
Posts: 1831
Joined: Mon Mar 07, 2005 10:33 pm
Contact:

Post by spiritplumber »

Updated! Uploading now.

File size when we're all done is about 180mb for the big file and almost 4mb for the small file.

www/spiritplumber.com/priv_remake See here :)


Changes:

* Everything moved to BFXM
* Tractor beam operation more WC-like
* Capships now eject escape shuttles
* Somewhat better AI in special cases (now a "self destruct" order is available if someone wants it -- other new orders are "take off every zig" for carriers, "eject one cargo" say for minelayers, and "coast to stop")
* Integrated the cargos from Gemini Gold plus a couple contributions
* Added base background computer screens (thanks!!!)
* Small capships are less prone to humping each other
* Flak gun looks better and actually blocks player sight, which was its main point
* Cargo slows to a stop if left alone long enough (unrealistic but practical, can be turned off)
* Little cleanups here and there
* A couple of new ships
* Capship turrets no longer get stuck in the top position (well not as much anyway)
* Turret explosions look a little better

NOTE: There are no sounds or music, just copy them over from Priv Remake 1.2 (not gemini gold) or better yet grab the new sounds.

NOTE: The units.csv files in the big upload and the patch differ in that the one in the patch still mentions .obj files rather than .bfxm, if you can't see a mesh after installing the patch just try to change the extension in there first. This is not a problem with the big upload. I want to get vs.exe to "figure itself out" as far as bfxm/obj goes (sometime today).

CVS will be updated Mondayish.



What to expect soon:

* No need to specify the extension in units.csv for .obj or .bfxm

* Eject-dock option for ships with a docking port

* A good recipe for chip dip
My Moral Code:
- The only sin is to treat people as if they were things.
- Rules were made for people, not the other way around.
- Don't deceive. Real life is complicated enough.
- If all else fails, smash stuff.
The Phantasm
Explorer
Explorer
Posts: 10
Joined: Wed Jul 20, 2005 5:23 pm

Post by The Phantasm »

> Can you cite an example that shows that MySQL does not support T-SQL? Everything looks pretty standard to me.

Yes, yes I can.

Try this.
http://www.nikmakris.com/2003/nov/17.html

Now, let's ask the MySQL developers:
http://dev.mysql.com/tech-resources/art ... osoft.html

While we're at it, let's look at a (slightly old, though not terribly outdated as databases change slowly) comparion.
http://www.mssqlcity.com/Articles/Compa ... m#part_5_1

MySQL does not support the "update from" t-sql command, which is surprisingly important.
http://archives.neohapsis.com/archives/ ... /1044.html

http://archives.neohapsis.com/archives/ ... /1044.html
smbarbour
Fearless Venturer
Fearless Venturer
Posts: 610
Joined: Wed Mar 23, 2005 6:42 pm
Location: Northern Illinois

Post by smbarbour »

I find your examples somewhat amusing. Granted that TOP is exclusive to MSSQL (although there is a superior function in ANSI standard SQL, which MySQL supports). What find funny in the first link is that it says that T-SQL support UCASE, LCASE, MID, and INSTR. Not one of these functions are in T-SQL. It says MySQL uses UPPER and LOWER for UCASE and LCASE respectively. These functions are supported by both. The page also shows that the LOCATE function in MySQL performs the same task as INSTR. Since T-SQL does not have an INSTR function, MySQL wins that argument. The only real function difference is the LEN function which is LENGTH in MySQL. This can be easily fixed by creating the corresponding function on EITHER platform since both allow user-defined functions.

2nd link: Okay, MySQL doesn't support INSTEAD OF triggers, I'll give you that.

3rd link: You're right. That is old. But it is terribly outdated. MySQL supports all of those features plus arrays that MSSQL does not support.

4th link: Funny, MySQL does support updating from another table via ANSI standard syntax. MSSQL does not support the standard though.

All this really proves is that MSSQL is inappropriate for the task. I think everyone here would agree on that.
I've stopped playing. I'm waiting for a new release.

I've kicked the MMO habit for now, but if I maintain enough money for an EVE-Online subscription, I'll be gone again.
The Phantasm
Explorer
Explorer
Posts: 10
Joined: Wed Jul 20, 2005 5:23 pm

Post by The Phantasm »

<chuckles> Well, I tried to help point out the problems with this whole approach, but hey, if you just want to tell me that I'm wrong, that's cool. Just for the record, if changing from XML to a CSV setup was as difficult and painstaking as someone said, this transition is going to be much worse. So you guys have fun with that.

Two months down the road, when the switch is still causing problems, just remind me then that I was wrong.
smbarbour
Fearless Venturer
Fearless Venturer
Posts: 610
Joined: Wed Mar 23, 2005 6:42 pm
Location: Northern Illinois

Post by smbarbour »

I'm not saying that this will be easy. In fact, I know that it will be a pain in the a$$! All I'm really saying is that it needs to be done if we want the project to advance to the point that we all want it to (and that would be multiplayer capable).

You seem to know at least as much about databases as I do. Do you have any other ideas for implementing a centralized data repository for a client-server based game? I know that neither XML nor CSV will cut it when we are talking about keeping track of every user in the game (plus NPCs if needed).
I've stopped playing. I'm waiting for a new release.

I've kicked the MMO habit for now, but if I maintain enough money for an EVE-Online subscription, I'll be gone again.
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

@chuck_starchaser

Were you ever successful with using the SQL engine with the simple Databases I sent you?
I know you believe you understand what you think I said.
But I am not sure you realize that what you heard is not what I meant.

Wing Commander Universe Forum | Wiki
Wing Commander: The Wasteland Incident
chuck_starchaser
Elite
Elite
Posts: 8014
Joined: Fri Sep 05, 2003 4:03 am
Location: Montreal
Contact:

Post by chuck_starchaser »

No, sorry mkruer; I couldn't figure out anything with the code that I had downloaded. Then I downloaded another demo that looked more promising, but I had another problem, and I can't remember what it was... I'll look at it again tomorrow and see if I can remember where I was at.
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

DB Update:
• Included Relationship for Multi group AI’s via category names
• Rename some fields to maintain consistence.
• Clarified some fields.

DB file Download 2MB http://vegastrike.sourceforge.net/users ... v0.1.9.mdb
This file contains only test data.

Image
I know you believe you understand what you think I said.
But I am not sure you realize that what you heard is not what I meant.

Wing Commander Universe Forum | Wiki
Wing Commander: The Wasteland Incident
spiritplumber
Developer
Developer
Posts: 1831
Joined: Mon Mar 07, 2005 10:33 pm
Contact:

Post by spiritplumber »

So, this would replace the various .csv and .xml files?

/clueless
//happy to learn though
My Moral Code:
- The only sin is to treat people as if they were things.
- Rules were made for people, not the other way around.
- Don't deceive. Real life is complicated enough.
- If all else fails, smash stuff.
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

That is the Idea. You would not be directly editing the file, but through a GUI that will be walked step by step through each item and any related items. The GUI should also prevent you from adding items that should not exist. Like saying you control a system before its discovered.

It is my ultimate goal place this on the web and allow users to login, make any changes to the DB and then the next time they play, you can get the latest update off the website.

One of the key advantage and reason for it near overwhelming complexity is because the database does not just span items, but can those items can change through time. A good example might be Elles Starbase, it changes three times from WC3 to WCP, each time with a different model. Now you can specify this is the model to use past this date. Here are the attributes to that model, etc…
I know you believe you understand what you think I said.
But I am not sure you realize that what you heard is not what I meant.

Wing Commander Universe Forum | Wiki
Wing Commander: The Wasteland Incident
klauss
Elite
Elite
Posts: 7243
Joined: Mon Apr 18, 2005 2:40 pm
Location: LS87, Buenos Aires, República Argentina

Post by klauss »

Oíd mortales, el grito sagrado...
Call me "Menes, lord of Cats"
Wing Commander Universe
Privateer Ferrius
Venturer
Venturer
Posts: 538
Joined: Tue Mar 21, 2006 12:54 am
Location: Delta Prime
Contact:

Post by Privateer Ferrius »

I could set up a testbox and write the SQL easy if I had the data, mkruer.
Cmdr. Micheal Ferrius (Ret.)
Retired Confederate Pilot

For Hire!
Post jobs to the BBS!

Tengoku de omachi shite imasu
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

There is data in the DB as is, but its test data, its accurate but its should still be treated as test data. We were holding off on releasing the DB because I need someone to peer review it and take a logical walk though all the relation ships to make sure we are not missing anything. Once the final DB is full mapped out, we are going to move it to MySQL and build an interface and allow people to populate the database online.

I know I am missing something, and it has to do with owning planets or stations. If you want walk through the image above and you should be able to find any information that you are looking either directly or through a relationship.
I know you believe you understand what you think I said.
But I am not sure you realize that what you heard is not what I meant.

Wing Commander Universe Forum | Wiki
Wing Commander: The Wasteland Incident
Zeog
ISO Party Member
ISO Party Member
Posts: 453
Joined: Fri Jun 03, 2005 10:30 am
Location: Europe

Post by Zeog »

I just had a look at the database structure and found that it is lacking i18n support. :?
That is, if one wants to translate the game the entire database has to be copied and then have names and descriptions translated. So, if a tweak of one value has to be applied one needs to change all copies of that database. However, on the other side it is not desireable that every user has to download all languages in one database. I think an i18n supporting approch would require "two databases". One for the stats and one for names and descriptions. Then only the latter one will need to be different if the user wants a localized installation. Alternatively, names and descriptions need to be stored in tables separated from other values.
Thinking about i18n support now at the design stage is important. Because adding it later could mean that everything has to be taken apart again which is somehow equivalent to "i18n is not going to happen".

And now just some conceptual questions: A MySQL database requires a local server for every installation, right? Presuming a database file is stored in "binary mode", SVN or CVS cannot be used (the entire file needs to be replaced if one little change occures) or one *has* to be online in order to make changes to the database. Is that right? Is that desireable then?
Privateer Ferrius
Venturer
Venturer
Posts: 538
Joined: Tue Mar 21, 2006 12:54 am
Location: Delta Prime
Contact:

Post by Privateer Ferrius »

I'll give that diagram a good look-through when I'm more awake.
Cmdr. Micheal Ferrius (Ret.)
Retired Confederate Pilot

For Hire!
Post jobs to the BBS!

Tengoku de omachi shite imasu
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

Zeog wrote:I just had a look at the database structure and found that it is lacking i18n support. :?
That is, if one wants to translate the game the entire database has to be copied and then have names and descriptions translated. So, if a tweak of one value has to be applied one needs to change all copies of that database. However, on the other side it is not desireable that every user has to download all languages in one database. I think an i18n supporting approch would require "two databases". One for the stats and one for names and descriptions. Then only the latter one will need to be different if the user wants a localized installation. Alternatively, names and descriptions need to be stored in tables separated from other values.

Thinking about i18n support now at the design stage is important. Because adding it later could mean that everything has to be taken apart again which is somehow equivalent to "i18n is not going to happen".
Sure We can do that. Figuring out the best way will take time, I don’t want to bloat the database tables.

Zeog wrote: And now just some conceptual questions: A MySQL database requires a local server for every installation, right?
Last time we did a review, no you would not have to install al full version to use it, there are many light weight versions out there.
Zeog wrote: Presuming a database file is stored in "binary mode", SVN or CVS cannot be used (the entire file needs to be replaced if one little change occures) or one *has* to be online in order to make changes to the database. Is that right? Is that desireable then?
There are really four options, Full, Differential, Change Logs (log shipping), and Custom query for all items altered after some given date.

Full is a Full backup at some given date.
Differential marks all changes from the last full.
Change Log keeps track of all changes and applies them to the current database. The good thing about this is that its always up to date. Bad part is that it tracks everything. So in some cases the logs could be larger then the actually data effected.
The custom Query is the most interesting, and the most complicated. Basically a query looks for all changes after a given, like a differential, but the main advantage is that it specify when it starts.

Little FYI last time I back up the forums, Over 100MB worth of data, the file compressed 5 to 1 I am estimating that diffs will on average be smaller then most models for the game. Tike will tell, ans when the DB gets to the point where the diffs are overly large, we will create a new Full, and start the process over again.
I know you believe you understand what you think I said.
But I am not sure you realize that what you heard is not what I meant.

Wing Commander Universe Forum | Wiki
Wing Commander: The Wasteland Incident
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

Privateer Ferrius wrote:I'll give that diagram a good look-through when I'm more awake.
HA! I know what you mean. You might want to reread the entire thread. I have been pretty good adding all additional items suggested.
I know you believe you understand what you think I said.
But I am not sure you realize that what you heard is not what I meant.

Wing Commander Universe Forum | Wiki
Wing Commander: The Wasteland Incident
Post Reply