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:

WCU Database Design & Documentation

Post by mkruer »

Ok I need someone to double check my DB relationship model. I think it works correctly. However after staring at it for days and days and days, it’s all a blur. The big change since the original is now the inclusion of a “keydateâ€
You do not have the required permissions to view the files attached to this post.
Last edited by mkruer on Tue May 01, 2007 1:50 am, edited 2 times 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
chuck_starchaser
Elite
Elite
Posts: 8014
Joined: Fri Sep 05, 2003 4:03 am
Location: Montreal
Contact:

Post by chuck_starchaser »

I would think the keydate class or record format should include a date and a .py filename. The .py script would evaluate the triggering, sometimes partly on the basis of the keydate, sometimes based on other conditions, such as the player having completed some mission. But I've never worked with databases so I might be out to lunch...
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

You bring up a valid point, however, let me explain it this way. The keydates are for dramatic/major events, such as:

introduction of a ship
introduction of a weapon
finding of a system (link)
founding of colonies or stations
some other predetermined natural disaster (destruction of kilrah)
etc…

Its not really meant to be a player executed event. That being said here is something interesting that could be done in the far future, if a WCU On-line ever comes into being. If the destruction of a facility occurs, that “eventâ€
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 »

Ok, but maybe the relations should be many to many with keydate; as otherwise you'd be limited to one keydate per system or planet. One might say that's enought, but then again, the artistic needs of storywriters are pretty unpredictable... Some very important system, like the capital system of Kilrah, or Sol, might be the scene of more than one universe-shattering event.
Actually, mkruer; I'm not sure you're not painting yourself into a corner with this keydate idea. Maybe we could use a separate table of historical timeline events. Say the main loop of the python game state machine could periodically take a look at the timeline database, in addition to all the other player state and triggers. This table would link to py files rather than systems, probably, as you might, say, want to move part of the story from one quadrant to another by just rewriting system names in the py scripts, and would not want to have to duplicate that by changing db links between systems and dates. Just my 2c.
If what you're trying to have is an immediate field you can look at to see whether a jump-point should be enabled, maybe jump points should simply have a boolean field for 'enabled'.

I know nothing about databases, but I like the OO design paradigm, and to whatever extent a "record" maps to an "object" I'd say the question to ask is, "does this structure map to the reality I'm trying to model?", which in the particular case of keydate, I'd probably say no; --you could say that planets are integral to systems, but keydates don't grow on systems, they are rather an external, subjective attribute.
spiritplumber
Developer
Developer
Posts: 1831
Joined: Mon Mar 07, 2005 10:33 pm
Contact:

Post by spiritplumber »

I would rather tie the advancement of "plot time" to player-triggered events, as in most adventure games -- repeating events (trade convoys leaving, and thus needing an escort, for example) on the other hand could happen in real time.

You don't want the player to say "Oh fudge, I missed the battle of Kilrah".

I also thought about doing a bit of retcon in that the player has the possibility to cover minor plot points of other WC games, such as
being hired to bring fuel to Angel's covert op mission behind enemy lines.
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.
klauss
Elite
Elite
Posts: 7243
Joined: Mon Apr 18, 2005 2:40 pm
Location: LS87, Buenos Aires, República Argentina

Post by klauss »

Let me get this: so you have multiple copies of records, and the one with the latest keydate is the active one?
If that's the case, appending an "active" boolean to the "keydate" record, and a python binding to evaluate that boolean, could solve most problems. That way, you could even bypass some events (although that would be tricky).
Oíd mortales, el grito sagrado...
Call me "Menes, lord of Cats"
Wing Commander Universe
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

chuck_starchaser wrote:Ok, but maybe the relations should be many to many with keydate; as otherwise you'd be limited to one keydate per system or planet. One might say that's enought, but then again, the artistic needs of storywriters are pretty unpredictable... Some very important system, like the capital system of Kilrah, or Sol, might be the scene of more than one universe-shattering event.
This is totally incorrect. Because many events can happen on a keydate within a system, hence the one date to many events. The only thing that the keydates prevents is two conflicting events happening at the same place, at the same time. A many too many relation ship is really bad DB design.
chuck_starchaser wrote:Actually, mkruer; I'm not sure you're not painting yourself into a corner with this keydate idea. Maybe we could use a separate table of historical timeline events. Say the main loop of the python game state machine could periodically take a look at the timeline database, in addition to all the other player state and triggers. This table would link to py files rather than systems, probably, as you might, say, want to move part of the story from one quadrant to another by just rewriting system names in the py scripts, and would not want to have to duplicate that by changing db links between systems and dates. Just my 2c. .
The idea behind the keydate is to have one universal history. Any deviation of this would be become an alternate reality which is an entirely different subject together, but this could be added directly to the DB by adding a new field specifically for that reality.
chuck_starchaser wrote:If what you're trying to have is an immediate field you can look at to see whether a jump-point should be enabled, maybe jump points should simply have a boolean field for 'enabled'.
This is one of the many ideas, but it also goes a little further then this by specifying influences and the level of influence within a system, or what type of economics take place.
chuck_starchaser wrote:I know nothing about databases, but I like the OO design paradigm, and to whatever extent a "record" maps to an "object" I'd say the question to ask is, "does this structure map to the reality I'm trying to model?", which in the particular case of keydate, I'd probably say no; --you could say that planets are integral to systems, but keydates don't grow on systems, they are rather an external, subjective attribute.
A little side note. I am not trying to say that a person can not change events. Such as the destruction of Kilrah, but that any events that happen before you start the game are already known. So think of the keydate as the starting point when you first play the game. Anything that takes place after a person begins to play the game, is by definition and alternate reality and as such later keydates have no relevance because again by definition you are in an alternate reality.
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 »

spiritplumber wrote:I would rather tie the advancement of "plot time" to player-triggered events, as in most adventure games -- repeating events (trade convoys leaving, and thus needing an escort, for example) on the other hand could happen in real time.
This brings up an interesting point, and what I like to call the elastic universe concept. In a nut shell the elastic universe means that although you might win a system back during WC1 timeline by the time you fast forwarded to WC2 (10yrs for those that do not know) any gains that you may have achived in the WC1 era have been brought back into the original timeline events specified in the DB to begin with. The amount of elasticity would depend on the level of the system you “conqueredâ€
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 »

klauss wrote:Let me get this: so you have multiple copies of records, and the one with the latest keydate is the active one?
If that's the case, appending an "active" boolean to the "keydate" record, and a python binding to evaluate that boolean, could solve most problems. That way, you could even bypass some events (although that would be tricky).

Bingo, you win the Cupie Doll!
:lol: :lol: :lol:

Edit: This is really not much difficult then what is currently out there, as a matter of fact, I would say its easier. Because the universe will automatically realign it self. The only thing you as a program would have to do is determine what data to ignore, and/or replace with your data. This is accomplished relatively easily, if you use the other data in the DB. To make things easier you could run a second DB in parallel, that would hold all the date the user has made to the Universe, and do a simple compare, and as you said not use what you don’t want.
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 »

Just for clarification, KeyDate, is the same as a snapshot date of the universe at any given time. The only reason why I call it KeyDate is because snapshot infers a global image of all change where KeyDate is just the singular event such the discovery of a new system or the rise of a system level.
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 »

I need help converting an MDB (Microsoft Access Database File) into a MySQL or even better a SQLite. I just want a simple easy way of doing it. The only information I need is the tables and the relationships, that’s it. Oh and it need to be free.
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 »

Is there any data in the tables? If not, send the MDB to me and I will generate the SQL script to execute on whatever SQL server you are using as long as it supports key constraints (it should since it is SQL-92 compliant IIRC)

smbarbour (at) gmail.com

EDIT: Unfortunately, I leave work now, so I probably won't be able to create the scripts until tomorrow.
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 »

Yes there is data in the file, and thats the problem I myself am not runnig SQL server, but at this point I guess I should set one up.
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 »

maybe this is a stupid question but, once the relationships are there, shouldn't moving the data be as easy as exporting it as text and importing as text (comma delimited or whatever)?
smbarbour
Fearless Venturer
Fearless Venturer
Posts: 610
Joined: Wed Mar 23, 2005 6:42 pm
Location: Northern Illinois

Post by smbarbour »

I will guess that you are using a Windows machine for the MDB. Send me the file, I will make some SQL scripts for it.

P.S. I would recommend that you have the SQL Desktop Engine (The free version of Microsoft SQL Server that's optimized for about 15 users) installed since I don't know if the SQL code to transfer the contents will work on another server. I just need the engine to handle the ODBC to ODBC work.

To anyone who wants to learn SQL, this will probably result in a plethora of examples to look at.
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.
chuck_starchaser
Elite
Elite
Posts: 8014
Joined: Fri Sep 05, 2003 4:03 am
Location: Montreal
Contact:

Post by chuck_starchaser »

The file is here.
Not as up-to-date as mkruer's but this is just for an experiment for now.
smbarbour
Fearless Venturer
Fearless Venturer
Posts: 610
Joined: Wed Mar 23, 2005 6:42 pm
Location: Northern Illinois

Post by smbarbour »

chuck_starchaser wrote:maybe this is a stupid question but, once the relationships are there, shouldn't moving the data be as easy as exporting it as text and importing as text (comma delimited or whatever)?
Relationships are where it becomes tricky since you would have to import the data in a specific order.

[Tech Jargon]
The database will not let you insert rows into a table that has a foreign key unless the values for the foreign key are already in the source table. i.e. If Table A references the row id from Table B, the value in Table A must already exist in Table B. In the event Table A references itself (which is allowed for a parent-child hierarchy), the referenced row must already exist before the row can be added.
[/Tech Jargon]
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 »

Ok Guys/Gals, here is a fun one, and perhaps the last piece of the puzzle for WCUDB.

Ships, Support Ships, Capital Ships, Stations, and Planets, what do they all have in common? They all have units to them, or they themselves are units to a large whole. So the question is how to “bestâ€
You do not have the required permissions to view the files attached to this post.
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 »

In the tbl_Space_Station you have acceleration, afterburner velocity, things like that. It's good if the intention is to be able to have self-propelled space stations in the future.

As for the commonality between them, if you have a tbl_Hidden_Unit which collects all the fields that are common to ship and station, then each of these can reference a record in hidden_unit to access those common characteristics.

tbl_Hidden_Unit
Name
Mass
CoreDamage
shields
armor
gunpower
comments

tbl_space_station
items_for_sale
items_for_upgrades
missions
hasmerchantsguild
hasmercsguild
Hidden_Unit_idnum

The ship table can itself be hidden, and reference hidden unit

tbl_Hidden_Ship
class
length
velocity
acceleration
pitch,yaw,roll
hasjumpdrive
hidden_unit_idnum...

Now small ships and cap ships can reference the hidden table
tbl_cap_ship
itemsforsale
itemsforupgrades
Hidden_Ship_idnum

tbl_small_ship
Hidden_Ship_idnum

The idea is, you never access any of the 'Hidden' tables directly, but only through the non-hidden tables, as the hidden tables represent nothing concrete, but are mere abstractions of commonality. Yet they save you from having to repeat code that deals with common attributes. I've never used databases; just describing how object oriented programming would take advantage of commonality, yet without mixing classes together.
smbarbour
Fearless Venturer
Fearless Venturer
Posts: 610
Joined: Wed Mar 23, 2005 6:42 pm
Location: Northern Illinois

Post by smbarbour »

I'm going to post my DB thoughts here (based on my experience with databases). I will edit this post, but I just wanted to let everyone know that I am working on it.

Okay, first off with regards to the various tables, there are some that are not needed (as-is in the diagram) such as tbl_Ship_Variants and tbl_Ship_Class. I can see why they are there (to restrict entry to a list of options), but it is of an inefficient design. Both of those tables should have a rowid that are used in the other tables. (This reduces the row size for the referencing tables. Saving a few bytes may not seem like much, but the extra bytes add up.)

Next, Chuck is on the right track, but he is perceiving the tables more like classes. I will forgo the table and field naming standards for my purposes, also I will be leaving out some parts for the sake of simplicity:

Code: Select all

Unit
----
RowID int
Name nvarchar(30)
Variant int (linked to Variants.RowID)
Class int (linked to Classes.RowID)
PhysicalProps int (linked to PhysicalProps.RowID)
MovementClass int (linked to MovementClass.RowID)
SensorProps int (linked to SensorProps.RowID not shown)
EnergyProps int (linked to EnergyProps.RowID not shown)
Mounts int (linked to Mounts.MountID)
Subunits int (linked to SubUnits.SubInitsID)
CommerceProps int (linked to CommerceProps.CargoID)
Upgrades int (linked to Upgrades.UpgradeID not shown)

Variants
--------
RowID int
Name nvarchar(30)

Classes
-------
RowID int
Name nvarchar(30)

PhysicalProps
-------------
RowID int
Length float
Mass float
CargoVol int
UpgradeVol int

MovementClass
-------------
RowID int
MaxVelocity int
MinVelocity int
Acceleration int
ManueverAccel int
CanJump bit
JumpCost int

Mounts
------
RowID int
MountID int
Weapon int (linked to Weapons.RowID not shown)
Ammo int
<location fields>

SubUnits
--------
RowID int
SubUnitsID int
Unit int (linked to Unit.RowID)
<location fields>

CommerceProps
-------------
RowID int
CargoID int
Inherits int (linked to CommerceProps.CargoID)
Item int (linked to Cargo.RowID not shown)
BasePrice int
DeltaPrice float
BaseQuantity int
DeltaQuantity float
Granted, there will likely be more fields in the tables defined here, but this is probably a good layout.
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 »

I'd rather leave units.csv alone honestly... replacing XML is fine with me (yay. in fact!) but the csv stuff Just Works as it is.
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.
smbarbour
Fearless Venturer
Fearless Venturer
Posts: 610
Joined: Wed Mar 23, 2005 6:42 pm
Location: Northern Illinois

Post by smbarbour »

I'm not sure that units.csv is working quite as well as it could.

OT- In regards to the VS CVS version, it seems that the cargo_import field of the CSV file is being truncated at some point during the load. It clearly shows that sensors and shady mechanic items should be showing up at Mining Bases at the very least (and its not a case of delta-quantity is preventing any quantity from showing as it does with merchant capships.)

As a bonus, by implementing the functionality of units.csv in a database instead, we gain the option of applying simple patches to the dataset rather than replacing the entire file. i.e. A new ship is available. Install this file, and it will be available in game.
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.
chuck_starchaser
Elite
Elite
Posts: 8014
Joined: Fri Sep 05, 2003 4:03 am
Location: Montreal
Contact:

Post by chuck_starchaser »

So, in databases there's no concept of data-hiding, or method hiding? In fact, does one write methods that are 'attached' to tables? Or are all methods global?

And, is there a chance you could send me a tiny SQLite file, with just two tables related to one another, say one item in each, for testing this c++ demo with? I just want to see if I can hack it into reading a table item and link from it to another table item.
smbarbour
Fearless Venturer
Fearless Venturer
Posts: 610
Joined: Wed Mar 23, 2005 6:42 pm
Location: Northern Illinois

Post by smbarbour »

Not really. The closest things to a "method" in SQL would be a stored procedure (an SQL script that has been optimized to return results a little faster) or a function (also an SQL script)

I've never used SQLite, but the following (without the comments) should do something you can test with.

Code: Select all

CREATE TABLE TableA (RowID int, caption varchar(10))
CREATE TABLE TableB (RowID int, caption varchar(10))

INSERT INTO TableA (RowID, caption) VALUES (1, 'Red')
INSERT INTO TableA (RowID, caption) VALUES (2, 'Blue')
INSERT INTO TableB (RowID, caption) VALUES (1, 'Green')
INSERT INTO TableB (RowID, caption) VALUES (2, 'Yellow')

SELECT * FROM TableA, TableB (older non SQL-92 format - SQL-92 format is SELECT * FROM TableA CROSS JOIN TableB

will return

1,'Red',1,'Green'
1,'Red',2,'Yellow'
2,'Blue',1,'Green'
2,'Blue',2,'Yellow'

SELECT * from TableA INNER JOIN TableB ON (TableA.RowID = TableB.RowID)

will return

1,'Red',1,'Green'
2,'Blue',2,'Yellow'
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.
Post Reply