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.
chuck_starchaser
Elite
Elite
Posts: 8014
Joined: Fri Sep 05, 2003 4:03 am
Location: Montreal
Contact:

Post by chuck_starchaser »

I tried both select statements, old and new style, and nothing happens...

Code: Select all

SQLite version 3.2.2
Enter ".help" for instructions
sqlite> 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 Table
   ...> INSERT INTO TableB (RowID, caption) VALUES (1, 'Green')
   ...> INSERT INTO TableB (RowID, caption) VALUES (2, 'Yellow')
   ...> SELECT * FROM TableA CROSS JOIN TableB
   ...> SELECT * FROM TableA, TableB
   ...>
But then again ".help" doesn't seem to work either... :-(
But then again nothing seems to affect it...

Code: Select all

   ...> .help
   ...> .HELP
   ...> HELP
   ...> help
   ...>
   ...> hello
   ...> quit
   ...> exit
   ...> cancel
   ...> abort
   ...> terminate
   ...> kill
   ...> discard
   ...> die
   ...>
Am I going to have to read the darn manual?
chuck_starchaser
Elite
Elite
Posts: 8014
Joined: Fri Sep 05, 2003 4:03 am
Location: Montreal
Contact:

Post by chuck_starchaser »

mkruer, once you figure a way to put your tables into SQLite format, and give me sequences of commands you need, I'll figure out how to wrap sqlite in C++; but I can't spend weeks learning sql to try and figure out how to do table conversions. So, let me know.
spiritplumber
Developer
Developer
Posts: 1831
Joined: Mon Mar 07, 2005 10:33 pm
Contact:

Post by spiritplumber »

I'm setting up a dynamic beowulf cluster at school so haven't got a ton done lately... just some modular tranposrts ^^; I want to do the final batch of ships then upload it.
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 just so used to SQL Query Analyzer (comes with MS SQL Server, which I use at work)

Two options here and I would recommend the first one or a mix of both.

after typing a line, on the next line type GO and press enter.

or, enter semi-colons at the end of each line and type GO when you are finished.

If you do a mix, I would recommend using the second method for the CREATE's and INSERT's and the first method for SELECT's.
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 »

Sorry about the delayed response guys/gal. I have been addicted to another game for the past week. After becoming frustrated and finally wining it, I hope I am done with my little detour and jumping back into the WCU fray. Anyway after thinking about the DB for WCU, I am becoming increasingly concerned, not that it will not work, or will not work better and faster, but rather how to support it, how is the community going to be able to add to the database? I seriously doubt everyone has access to MS Access or knows even how to use even the most rudimentary SQL DB utilities. I think that this also need to be addressed before we move to a DB backend. We need to have some sort of interface or better yet have it online so people can make changes to the database and then extract all the information (i.e. download the most current version of the DB) This I will definitely need help on.

On a side note, I think I have figured out all the ship/unit stuff, but it’s one of these things that is easier for one person to sit down a think out everything before hand, and then come back for critical review.
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 theory at least, you can give the database any interface you want, using C++ and/or Python; and you could even fill the data in some of the tables from xml files. That's what I wanted to do: Make a C++ class representative of any table record. And if you could supply me with an SQLite version of your database, I could probably do so in no time. Basically, what the sample program I got does is issue commands, any commands, to SQLite from within C++. So then, instead of inscrutable sql statements, we can just have clearly named class functions that wrap all the ugly sql stuff.
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

Good news on the DB front, I think I have finished with all the tables. And the total comes out to around 30.

I have made some fundamental changes to classification of units (Ships, Stations, Support Ships, etc…)

Because all these Units are fundamentally the same, and carry many of the same attribute, the only way to distinguish between a ship and a Capital ship is to give it fictional classification. For that reason all ships carry five types of Classification. Of all the Classifications only ONE is required.
The Four types of Classification, Category, Type, Class, and Variant.

Category is broken down into the following:
Small Craft = Fighters and Bombers
Support Ships = Transports
Capital Ships = Carriers and Destroyers
Stations = Self explanatory.
Planets = Self explanatory.

The next grouping is Type, this break it down further its more basic components, mentioned above, Fighters, Bombers, Carriers, Destroyers, etc…Finally there is the Class and Variant, Class is basically the name of the ship, i.e. Arrow. Variant is how the ships is configured. Standard, Long Range Interceptor, etc…

So in the example of an Arrow Fighter, the full classification would be.

Category = Small Craft
Type = Light Fighter
Class = Arrow
Variant = Standard

Hope that makes sense.

Weapons have been broken into three types, Guns, Missiles, and Turrets, each with its respective statistic (Note: The weapons mounted on a turret is pulled from the Guns and Missiles table.)

Now for the Big Question, right now I only have one variable which is PathFile that will point to all meshes/scripts/etc…All scaling and rotational alignment is done outside of the DB, the main reason for this is its easier to modify on a user level, and it would be ungodly complicated to have it directly in the DB.

Here comes the fun part all ships from now on having only mount point information, no weapons are directly attached to the ship, but rather looked up in the DB. This will allow anyone to take any ship, and providing that the ship has the proper mounts, can add any type of weapon you so choose. Needless to say most weapons for ships will be either Light or Medium mounts, and only bomber and cap ships will have heavy mounts.

Missiles will also be the same way. With ships either having Light, Medium, or Heavy mounts.

I will grab a screen shot of the new relationship map after work.
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 »

What about rail-guns and chain-guns? These would be "internal" weapons, that are mounted in the body of the ship. Chainguns would use old-style slugs and therefore use no energy to fire, but are limited ammo.

There may be many more attributes to weapon mounts in the future. Some may be auto-tracking expandable, some may not. Some may have extra heavy wiring. Some may have provision for coolant circulation, and be compatible with weapons that require forced cooling to operate.

All I'm trying to say, make sure you don't design the data base in a way that cements the way we are doing things now, and make future growth difficult.

Also, your units don't mention buoys, for instance. Probes, also, if you've played Homeworld, I'm thinking we should have them: You can shoot a probe in the direction of an enemy formation very far, like light-seconds away. The probe travels at high speed and, at the last moment, takes pictures of the enemy fleet and radios them back.

There could also be stand-off weapons like long range torpedos and cluster bombs.

Mines and mine-layer ships.

Spy units like stealth cameras you lay like mines, and you can request pictures taken by it from far away.

Are repair bots a kind of "unit"?

How about these cargo units that collect ore and come back automatically?
smbarbour
Fearless Venturer
Fearless Venturer
Posts: 610
Joined: Wed Mar 23, 2005 6:42 pm
Location: Northern Illinois

Post by smbarbour »

That's one of the selling points of using a database. It is easy to add fields to a table. If the program doesn't have a use for the fields yet, it can simply ignore them.
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 »

Excellent. That's what I wanted to know. Thanks.
smbarbour
Fearless Venturer
Fearless Venturer
Posts: 610
Joined: Wed Mar 23, 2005 6:42 pm
Location: Northern Illinois

Post by smbarbour »

@chuck: How did the SQL testing go? Did those changes work for you?
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 »

I gave up. Coudn't understand a thing what's going on, and frankly, I don't want to learn SQL. All I'm willing to do is provide a C++ wrapper for sql commands, so that we can create classes and functions with friendly and obvious names, that hide the sql command complexities. I think I can do it, but I need an SQLite db that I can try and open so as to test the software.
But I don't know how to create such an SQLite db, and frankly I don't care to learn how to do it. As soon as someone can give me one, I'll get back to work on the wrapper thing.
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

@chuck_starchaser, Also the term Hard Points, is meant to ambiguous, it not internal nor external. Also on the subject of Auto tracking, the real trick is not with the DB, I was planning to allow more then one weapon in any spot as long as it fits Light = one slot, Medium = 2, and Heavy = 4

Such things at Auto tracking would take up an additional slot, as will as extra ammo.

After thinking about it, there may need to be one more level, after Heavy, such as AntiCap this would be for the Devastators Bomber Main Cannon. This would require 8 Units or something. This needs to be worked out before the first implementation of the ship. The DB part is easy to add, its making sure everything on the model is correct that is the hard part.
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 »

Why *numbers* of slots? I would speak of slot *types*. The railgun is an idea I've been playing with for a while in my mind. Most medium to large ships would have a railgun built into the hull, running along the center of the ship's body. It would be able to take out a capital ship of a single shot, but, to become operational as a stand-off weapon that it is, you'd have to get a number of upgrades: At least 4 synthetic aperture passive radars, a synthetic aperture extender interface that allows 4 such radars to operate as a single unit for ultra-remote sensing, a telescopic view-finder, a very large capacitor, and a gyroscope, for very fine control of ship attitude with all thrusters and jets turned off. When you fire a slug, the capacitor is completely discharged and the power draw for the next minute or so is so high that lights in the ship dim, the fans slow down and the computers and HUD reboot. :)

Anyways, some ships would not even have a liquid helium cooling system; others would. Some weapon slots would have a coolant connection, and these would be compatible with weapons that require it.
To be autotracking compatible, a weapon slot would have to have a wider flat surface with threaded holes for bolts, extra wiring, a fiber optic channel so that it can receive fresh target information, plus a sensor slot right next to it so that it can sense inaccuracies due to torsion of the wing, and make corrections to the info from the main targeting computer, on the spot.
Thus, cheaper ships would simply not have the facilities necessary for autotracking expansion installation. And of those ships that do, some might support larger autotracking cones than others, and some would support larger weapons than others. At the lowest level you could get a ship with 2 autotracking compatible mounts, but they might only support light weapons and only a 2-degree autotracking correction cone. Largest cone should be 4 degrees, to be realistic. At the highest end, you could get a ship with 4 autotracking fixe wing mount slots supporting large size guns, and with coolant facilities to boot. Not that I wouldn't rather have turrets, myself.

Anyhow, I'd much prefer to qualify things like mounts by detailed attributes than have some magic numbers associated with them. Configuring one's ship is probably the most fun part of the game, and I'd like it to be a science and an art, no simplifications at all.
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

I would rather keep with the KISS. This is primarily a WC DB, and I would prefer to stick to the WC guidelines, that mean no “newâ€
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 »

A better idea would be to make your rail cannon a turret mount. Then you are pretty well unlimited in your design. There is no reason why you can not do this. Its been done in WCP SO with the Hades. I very effective weapon, little slow but one shot one kill if you get in its way.
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 »

I guess that the Phase Transit Cannon on the Concordia counts :)
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 »

yes I guess that should be included also.
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 »

OT
I think of the railgun as the ultimate example of stand-off, one shot and there goes a carrier, weapon. The last type I would think of mountable on a moving turret. A 20-minute reload thing (requires soldering), that causes a blackout when you fire it.
Maybe capships could feature railguns on rotating platforms... doubt it though.
/OT
spiritplumber
Developer
Developer
Posts: 1831
Joined: Mon Mar 07, 2005 10:33 pm
Contact:

Post by spiritplumber »

Kinda removes the point of fighters 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 »

As Promised, here is the Relationship model. I highly recommend downloading the ZIP file and printing out the full PNG file.
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
spiritplumber
Developer
Developer
Posts: 1831
Joined: Mon Mar 07, 2005 10:33 pm
Contact:

Post by spiritplumber »

Looks good, other than turrets are subunits and not weapons (in fact, if you go look you will see that each turret is TWO subunits)
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 »

spiritplumber wrote:Looks good, other than turrets are subunits and not weapons (in fact, if you go look you will see that each turret is TWO subunits)
Look closer that’s why Turrets have a separate Loadout Table. This allows for a Turret to have many gun or many missiles or any combination there for of. The trick with any DB is to realize what the One-to-One or One-to-Many relationships mean. I know from design that I often stump myself until I re-realize that in order to allow an object to have many parts, it needs its own table that then reference back to the object you were trying to add to.

Anyway the bigger think is coming up with a decent schema for weapon systems. I don’t think that the 1,2,4 method will work, and I am not quite sure how to implement it so you can have many slots at different locations and of varying capacity’s.

This is all subject to change, but it looks like there is going to be two class of Gun system, one for Smaller craft and one for Cap Ships, this need to be addressed to allow for the Behemoth and the Concordia Phase Cannon, and possibly the Midway’s One shot wonder cannon.

As I said above, I want to keep it simple.

For people would want Auto tracking on a weapon, it will take up an additional slot. For those that want to add longer range, that would take up an additional slot. Etc…

On the topic of extras, does anyone have an up to date list of all the ship extras that can be added, and what are the benefits?
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 »

Here you go, its finally here … hopefully. This comes out of the WCUDB 0.1.4 branch. It will undoubtedly change a few more times, but this should give you a good starting point and it contains some data as well. In the Zip are two files, a SQL Dump file (Raw SQL Data) and DDB file which should be SQLite 2.1 version of the database. Either way let me know if it works.

Edit: BTW the tool i used to finaly convert the thing in one step was DBTools Manager Professional you can find more info at http://www.dbtools.com.br/EN/index.php
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 »

Thanks. I'm working on another piece of audio code, atm, but I should be able to get back to this in the weekend. I notice the file extension is .ddb, wheras my demo software uses extension .db; are they the same thing?
Post Reply