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 »

God I hope so. But I dont have the file to compare.

Edit: Yes! It looks like it, it has the same data format. i.e. (This file contains an SQLite 2.1 database)

Sweet Finaly None of this Access to MySQL to SQLite now its one hop.

BTW your Program just overwrites any DB file. so you need to figure out how to make it not creat the DB but just read it.
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 »

mkruer, what would be the commands to read data from a table in the WCUDB.DDB file?
I got the program to open the database file, but I don't know how I read from a table in it. I tried the command

Code: Select all

select * from foo
from another example, just to try and see what would happen (i know there's no foo in it, but just for a test, since I don't know what's in it) and the program crashed with 'illegal operation'.

You know what I'm thinking? If there's any way to store the database as separate tables in separate files, that will be better for cvs, as many people may want to add columns here, rows there, and cvs will have a headache trying to reconcile the mods coming in.
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

The command should be

Code: Select all

select * from [table name]

You can try "tblUnit" and it should return all values in that table

You can look at the SQLite documentation for additional information at http://www.sqlite.org/sqlite.html

As to your second question. Yes I was thinking something along those lines however there is a slight problem with this. Because it’s a DB if you make a change to one file, it could possible affect all tables.

Phase 1: I want to get the DB running
Phase 2: Implement a web based DB and interface so people can make changes to the DB on SF and then export all the data upon request to be used directly by the program. This may require additional programming to read/import *.SQL (a common SQL file format) directly into the DB on the local system, but it would meat all your requirements.

As a matter of fact you might want to look into the *.SQL now because I know that can be read directly my all SQL packages.

The web interface is going to be the biggest bitch. I could use Access and export an ASP page form what I have built now, but SF might have issues with that. I have never tried.
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 »

Okay, thanks, that worked; now...

Code: Select all

int main()
{
    SQLiteWrapper sqlite;
    if (sqlite.Open("WCUDB.ddb"))
        std::cout << "WCUDB.ddb created or opened" << std::endl;
    else
        std::cout << "couldn't open WCUDB.ddb" << std::endl;
    SQLiteStatement* stmt = sqlite.Statement("select * from tblUnit");
    std::cout << "> select * from tblUnit" << std::endl;
    while (stmt->NextRow())
    {
        std::cout << stmt->DataType   (0) << " - " << stmt->DataType   (1) << " | " <<
                     stmt->ValueString(0) << " - " << stmt->ValueString(1) << std::endl;
    }
    return 0;
}
The above outputs...

Code: Select all

WCUDB.ddb created or opened
> select * from tblUnit
then crashes with "illegal operation".

In other words, the stuff in the while loop's crashing. It came from an online example. Anything looking funny to your eyes?
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

I am wondering if you have to explicitly define the parts of the table. Here is an excerpt from the SQL file that might fill in the blanks

Code: Select all

Unit_IDNum] integer NOT NULL , 
	[KeyDate_IDNum] integer CONSTRAINT fk_tblUnit_KeyDate_IDNum REFERENCES tblKeyDate (KeyDate_IDNum), 
	[Item_IDNum] integer CONSTRAINT fk_tblUnit_Item_IDNum REFERENCES tblItems (Item_IDNum), 
	[Unit_Type_IDNum] integer CONSTRAINT fk_tblUnit_Unit_Type_IDNum REFERENCES tblUnit_Type (Unit_Type_IDNum), 
	[Unit_Class] varchar (255), 
	[Unit_Variant_IDNum] integer CONSTRAINT fk_tblUnit_Unit_Variant_IDNum REFERENCES tblUnit_Variant (Unit_Variant_IDNum), 
	[Faction_IDNum] integer, 
	[Unit_Length] float, 
	[Unit_Velocity_Max] float, 
	[Unit_Velocity_Default] float, 
	[Unit_Velocity_Min] float, 
	[Unit_Velocity_Acceleration] float, 
	[Unit_Afterburner_Velocity] float, 
	[Unit_Afterburner_Acceleration] varchar (255), 
	[Unit_Afterburner_Fuel] varchar (255), 
	[Unit_Pitch] float, 
	[Unit_Yaw] float, 
	[Unit_Roll] float, 
	[Unit_Pitch_Acceleration] varchar (255), 
	[Unit_Yaw_Acceleration] varchar (255), 
	[Unit_Roll_Acceleration] varchar (255), 
	[Unit_Jump_Capability] boolean, 
	[Unit_Jump_Fuel] varchar (255), 
	[Unit_Mass] float, 
	[Unit_Core_Damage_Points] varchar (255), 
	[Unit_Shield_Bow_Front] float, 
	[Unit_Shield_Aft_Rear] float, 
	[Unit_Shield_Port_Side_Left] varchar (255), 
	[Unit_Shield_Starboard_Side_Right] varchar (255), 
	[Unit_Shield_Phased] float, 
	[Unit_Shield_Recharge_Max] float, 
	[Unit_Shield_Recharge_Default] float, 
	[Unit_Shield_Recharge_Min] float, 
	[Unit_Armor_Bow_Front] float, 
	[Unit_Armor_Aft_Rear] varchar (255), 
	[Unit_Armor_Starboard_Side_Right] varchar (255), 
	[Unit_Armor_Port_Side_Left] varchar (255), 
	[Unit_Gun_Power] varchar (255), 
	[Unit_Gun_Recharge_Max] varchar (255), 
	[Unit_Gun_Recharge_Default] varchar (255), 
	[Unit_Gun_Recharge_Min] varchar (255), 
	[Unit_Loadout_IDNum] integer CONSTRAINT fk_tblUnit_Unit_Loadout_IDNum REFERENCES tblUnit_Loadout (Unit_Loadout_IDNum), 
	[Unit_Description] varchar (255), 
	[Unit_PathFile] varchar (50)
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 »

If I try,

Code: Select all

E:\Zips\SQLite\sqlite3.exe c:\windows\desktop\sqlx\wcudb.ddb
I get...

Code: Select all

SQLite version 3.2.2
Enter ".help" for instructions
sqlite> select * tblUnit
   ...> ;
SQL error: near "tblUnit": syntax error
sqlite> select * from tblUnit
   ...> ;
SQL error: file is encrypted or is not a database
sqlite>
Do I need a password? :D
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

No Password, and it should not be any encryption.

Looking around on goggle, it sounds like the DB engine is having trouble with foreign keys (Key data referenced in another table). My guess is that you are going to have to load the entire DB in order to pull information from one table (before you freak out, this is normal DB operation, you cant half open a DB)

http://www.justatheory.com/computers/da ... ggers.html

If you want, when I get home I will create a fictitious table that only contains one table and no foreign keys
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 »

To the extent I get the idea about these triggers they sound like a good idea. Maybe we could integrate them with the wrapper.
That'd be a good idea, about the single table. Need to start simple with this wrapper stuff. Once I can read one table, I'll see about handling 2 tables, then all the rest.
spiritplumber
Developer
Developer
Posts: 1831
Joined: Mon Mar 07, 2005 10:33 pm
Contact:

Post by spiritplumber »

my head hurts :shock:
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 »

chuck_starchaser wrote:To the extent I get the idea about these triggers they sound like a good idea. Maybe we could integrate them with the wrapper.
That'd be a good idea, about the single table. Need to start simple with this wrapper stuff. Once I can read one table, I'll see about handling 2 tables, then all the rest.
But that violates my rule. Cram everything in there on the first attempt. Try to get it to work. Fail. Then give up.
LOL
Last edited by mkruer on Fri Jul 15, 2005 11:47 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
chuck_starchaser
Elite
Elite
Posts: 8014
Joined: Fri Sep 05, 2003 4:03 am
Location: Montreal
Contact:

Post by chuck_starchaser »

Eventually I meant, not now.
Anyways, soon as you can give me a single table file I can try and read it.
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

Here are two simple databases, the first one (test1) is just a single table, the second one (test2) is two tables with a single link between them. Refer to the *.SQL.txt files for table and field names
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
klauss
Elite
Elite
Posts: 7243
Joined: Mon Apr 18, 2005 2:40 pm
Location: LS87, Buenos Aires, República Argentina

Post by klauss »

Honest. Why not mysql? It can be statically linked, and become part of the app (no need to setup servers or anything). I never did that, but I know it's possible.

I say since, from the looks of it, SQLite is giving quite some trouble. Mysql's C interface is easy and straightforward. It even comes with a C++ interface (although I prefer the C interface - more DB-like, and you can code your own special-purpose interface on top of it).

Besides, from the looks of it, we'll need full database support, and mysql is about to provide it (it still lacks SP, IIRC, but everything else is in place).
Oíd mortales, el grito sagrado...
Call me "Menes, lord of Cats"
Wing Commander Universe
crouton
Bounty Hunter
Bounty Hunter
Posts: 161
Joined: Wed Mar 23, 2005 11:03 pm

Post by crouton »

I'm not a database guru, but isn't PostgreSQL (spelling?) a little more robust at the expense of initial ease of setup?
klauss
Elite
Elite
Posts: 7243
Joined: Mon Apr 18, 2005 2:40 pm
Location: LS87, Buenos Aires, República Argentina

Post by klauss »

They say so, but I've never had trouble with MySQL.
Oíd mortales, el grito sagrado...
Call me "Menes, lord of Cats"
Wing Commander Universe
chuck_starchaser
Elite
Elite
Posts: 8014
Joined: Fri Sep 05, 2003 4:03 am
Location: Montreal
Contact:

Post by chuck_starchaser »

I know less than you guys subtracted from one another, but couldn't MySQL or PostgreSQL be used for the server, and SQLite for the clients, or these aren't compatible beyond the pretty sounding names?

(Thanks, mkruer, I'll play with it in the morning.)
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

Although MySQL, PostgreSQL and SQLite are very much alike, the way they store data internally is different from each other. Also there may or may not be conflicts with the SQL queries.

MySQL is the most prevalent, and has the most support, however for client side application it might be overkill.
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 »

@chuck_starchaser:
Have you tried the smaller db i have set you? also have you looked at MySQL if this SQLite is giving you too much greif?
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 »

I think it all depends on whether the queries use functions specific to a certain database. If it is limited to just fields and joins, there shouldn't be any problem. SQL is just a common language for accessing databases. The only incompatibilities come from vendor-specific extensions to the language. (Microsoft is especially bad about it when it comes to outer joins. I prefer the Microsoft syntax {select foo, bar from t1, t2 where t1.id *= t2.id} instead of {select foo, bar from t1 LEFT OUTER JOIN t2 ON t1.id = t2.id})

For those who don't understand what an outer join is, this example will return all rows from t1 showing blank values for fields from t2 where t2 does not have a matching record in t1.
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 »

Sorry, mkruer; I missed your post. Indeed, it's pretty hard for me, not knowing anything about databases in general, sql in particular, to try to make the wrapper work, since I can't easily come up with ideas to test.; --not that I'm giving up on it. I haven't tried the last table you uploaded, partly because I don't even know what sql statements to try. The wrapper, all it does is send an sql command, in the form of a text string, from inside c++ to the sqlite client. But I can't come up with sql commands off the top of my head. When I look at sql commands, like "SELECT FROM X whatever" that's greek to me.

Right now, I'm working on these screens for PR/WCU, and having a good time at it. And I can let you guys discuss and decide whether you really want to use sqllite or sqlheavy or what. IOW, I'm procrastinating... :)
The Phantasm
Explorer
Explorer
Posts: 10
Joined: Wed Jul 20, 2005 5:23 pm

Or...

Post by The Phantasm »

I have a few comments and suggestions on this entire approach.

First, you have a storage system (XML) that works. A working system
is not something to throw away lightly.

If you are intent on changing over to SQL, I would suggest that first
you write a database interface layer. Build that layer to work with the XML system that currently exists, then integrate that layer into
the software. When the interface has been sufficiently tested and
debugged, moving to SQL will be as difficult as setting up the database
(which is not a small task at all) and implementing the database interface in another class that interfaces itself to SQL instead of to XML files.

Also, since you will still have the XML system lying around, you will be
able to quickly and effectively test while building the SQL segment.


Now, I'm also confused as to why there is the push to move to SQL
at all. SQL has a lot of strengths and a lot of weaknesses. SQL is
very good at managing large numbers of records and anything that
involves basic set theory. Pulling out records that match certain
criteria are great. The use of constraints is great. But, SQL has a
lot of downsides. First, any install of the game will require an install
of a SQL database. That's a big deal. Second, you are likely to
introduce bugs in the SQL code itself. Doing something complex in
SQL is difficult, and most of the free SQL engines are not very
powerful. MySQL is nice, but its support for powerful SQL features
is quite lacking. Now, in theory, the latest version of MySQL supports
stored procedures and the like, but that's just going to slow things
down. This game is not going to be munching on a million records
at a time, which is what SQL excels at.

Again, if you build a database interface in the code and implement
it using the existing XML system, you will still have the option of using
SQL later.

It seems to me that no one involved has done a lot of work with SQL.
When you decide to use SQL, you are forcing your data design to
fit into a relational mold. This is not a problem for a lot of things, but
scripting systems don't necessarily follow in a relational model. You
should probably design your data system first and then decide if SQL
is a good fit, or if you would be better off using something else.
XML is not a bad system, especially for prototyping systems or any
system that involves hand editting the data. If you want to speed the
system up, look at replacing the XML system with a custom binary
system (which is NOT to say just serialize your objects to disk, because
that has a host of problems of its own).


In summary, I think the project would gain the most from leaving the
current, functional, system alone. If there is a forseeable problem with
the current system, then building an abstraction layer between the
existing game and the existing data system will help ease the move to
a new data system. Moving to a SQL system for all data storage will
derail the project for at least a month. That time can either come
immediately or later merging the changes back into the main trunk.

Is there a debilitating problem with the current system? If not, I think
that a move to SQL should be delayed until it becomes necessary. It
seems that there are plenty of other additions that could add to the
project value much more than this proposed move to SQL.
mkruer
Site Administrator
Site Administrator
Posts: 1089
Joined: Thu Jan 02, 2003 10:07 am
Contact:

Post by mkruer »

I think I can sum this up quickly. First XML parsing is extremely slow. To give you an idea, originally the entire engine was using only XML, and took several minutes to load even the simplest scripts. When we went to csv files there was a tremendous speed boost for accessing data. Secondly with SQLite, there is no SQL installation. required, it is internal engine, and it’s a simple function call with in the program. If we go to MySQL there may be additional overhead, and MAY (TBD) require a local side SQL server install (we are still looking in to this) Third is data integrity, I don’t know how many times people have mad one change to one location only to have it blow up something else down the line because the change was suppose to affect only one file, but ended up wiping out a lot of other “stuff.â€
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 »

1) Parsing XML can be slow. Parsing it is quick, building the tree is quick, but the dynamic mapping of that information into class structures tends to use reflection, and reflection is _slow_. If the XML itself isn't terribly complex, why not try running some speed tests on writing a custom parser instead of using the standard (slow) XML readers?
The problem is definitely not the text itself. I had a program that read in 750 small bitmaps, and because of the way bitmap reading worked, that took 5+ minutes. I loaded the images and wrote them out to a simpler binary file (that ended up being larger than the sum of the bitmaps), and that file could be loaded in in about a second. The problem isn't the storage format, the problem is that the standard XML tools are slow. They can be rewritten (especially if the XML isn't terribly complex).

2) Ok, but there is still the complexity of writing the SQL scripts.
And for MySQL, yes, you will need a server somewhere, local is
probably best.

3) So you expect SQL to solve this problem how? I assume you intend to setup foreign keys and unique constraints and the like. That will help. Alternatively, if there was just a custom editor for the XML that verified the data (data verification is always nice anyway), that would accomplish the same goal, right?

4) Yes and no. Mostly no. If you're going to be running online, you're probably going to need custom protocols and such, and you're going to need to hide the database itself from the users to protect the speed an integrity of the database. This is where the intermediate layer comes in handy as well. Ideally, the software should not know _how_ it gets its data. It should pass through a data layer, and that layer should be relatively easy to replace. Then you can put things online on a central server and deal with that issue when you come to it, way down the line, which is to say, definitely not a week from Thursday.

5) The disadvantage of a CSV is that you lose the readability of the XML. Why not just write a custom XML parser? Or hell, I'll help you do it. I know that people have this belief that parsing text is slow, and it is compared to parsing such things as fixed length binaries, but anything under 10 meg of text should be parsable in a second or two.
smbarbour
Fearless Venturer
Fearless Venturer
Posts: 610
Joined: Wed Mar 23, 2005 6:42 pm
Location: Northern Illinois

Post by smbarbour »

What I am trying to understand is why everyone wants to jump on the XML-for-data bandwagon. XML has its uses. Configuration files are a good use. Databases are a poor use. It is human readable, but is unnecessary overhead for machines to read. XML files are not indexed. SQL is. If I tell the computer to give me the information on a Paradigm. With XML, I need to load the file and parse it to find that requested data. SQL will look at the index and say the Paradigm is entry such-and-such. Here is your record.

XML does not scale well and as an immense potential for redundant data. In the instance of ships (which are stored in CSV, but that is inefficient as well), if the ship has 20 turrets of type x, I will need to parse the file 20 times to look up the info for that turret. You may say, "just look it up once," but what if I had 10 turrets of type x and 10 of type y and they are interleaved "xyxyxyxyxyxyxyxyxyxy". Contrary to how some people think, SQL scripts are rather straightforward.

Furthermore, you suggest:
The Phantasm wrote:If you want to speed the
system up, look at replacing the XML system with a custom binary
system (which is NOT to say just serialize your objects to disk, because
that has a host of problems of its own).
So you would rather the developers create their own database system from scratch rather than using tried-and-tested systems that already exist? If ODBC is used (granted that its not perfect), any number of options become available. Database systems become interchangeable. If someone running Linux already has MySQL or PostgreSQL or whatever installed, the game can use it. On Windows, the game could use an Access file or the Microsoft SQL Desktop Engine, or any number of other database systems that are available.

When the core game (as well as the various mods) become multiplayer, XML will not even come close to the efficiency of a proper database system.

For the record, I am a database admin. I know what I am talking about.
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