WCU Database Design & Documentation
-
- Site Administrator
- Posts: 1089
- Joined: Thu Jan 02, 2003 10:07 am
- Contact:
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.
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
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
-
- Elite
- Posts: 8014
- Joined: Fri Sep 05, 2003 4:03 am
- Location: Montreal
- Contact:
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
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.
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
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.
Latest version of Cinemut Opaque
Latest version of LaGrande noodleworks (scroll down).
An evolving La Grande How-To...
The non-working, but latest, CineMut test_bike
PU (Privateer: Parallel Universe's Home). WC or Privateer Drayman for you?
WCpedia --The Wing Commander Encyclopedia-- From Angel Deveraux through Belisarius to Zachary Banfeld...
WC Nexus forum, the Moonbase Tycho of WC fans.
Latest version of LaGrande noodleworks (scroll down).
An evolving La Grande How-To...
The non-working, but latest, CineMut test_bike
PU (Privateer: Parallel Universe's Home). WC or Privateer Drayman for you?
WCpedia --The Wing Commander Encyclopedia-- From Angel Deveraux through Belisarius to Zachary Banfeld...
WC Nexus forum, the Moonbase Tycho of WC fans.
-
- Site Administrator
- Posts: 1089
- Joined: Thu Jan 02, 2003 10:07 am
- Contact:
The command should be
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.
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
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
-
- Elite
- Posts: 8014
- Joined: Fri Sep 05, 2003 4:03 am
- Location: Montreal
- Contact:
Okay, thanks, that worked; now...
The above outputs...
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?
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;
}
Code: Select all
WCUDB.ddb created or opened
> select * from tblUnit
In other words, the stuff in the while loop's crashing. It came from an online example. Anything looking funny to your eyes?
Latest version of Cinemut Opaque
Latest version of LaGrande noodleworks (scroll down).
An evolving La Grande How-To...
The non-working, but latest, CineMut test_bike
PU (Privateer: Parallel Universe's Home). WC or Privateer Drayman for you?
WCpedia --The Wing Commander Encyclopedia-- From Angel Deveraux through Belisarius to Zachary Banfeld...
WC Nexus forum, the Moonbase Tycho of WC fans.
Latest version of LaGrande noodleworks (scroll down).
An evolving La Grande How-To...
The non-working, but latest, CineMut test_bike
PU (Privateer: Parallel Universe's Home). WC or Privateer Drayman for you?
WCpedia --The Wing Commander Encyclopedia-- From Angel Deveraux through Belisarius to Zachary Banfeld...
WC Nexus forum, the Moonbase Tycho of WC fans.
-
- Site Administrator
- Posts: 1089
- Joined: Thu Jan 02, 2003 10:07 am
- Contact:
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
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
-
- Elite
- Posts: 8014
- Joined: Fri Sep 05, 2003 4:03 am
- Location: Montreal
- Contact:
If I try,
I get...
Do I need a password?
Code: Select all
E:\Zips\SQLite\sqlite3.exe c:\windows\desktop\sqlx\wcudb.ddb
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>
Latest version of Cinemut Opaque
Latest version of LaGrande noodleworks (scroll down).
An evolving La Grande How-To...
The non-working, but latest, CineMut test_bike
PU (Privateer: Parallel Universe's Home). WC or Privateer Drayman for you?
WCpedia --The Wing Commander Encyclopedia-- From Angel Deveraux through Belisarius to Zachary Banfeld...
WC Nexus forum, the Moonbase Tycho of WC fans.
Latest version of LaGrande noodleworks (scroll down).
An evolving La Grande How-To...
The non-working, but latest, CineMut test_bike
PU (Privateer: Parallel Universe's Home). WC or Privateer Drayman for you?
WCpedia --The Wing Commander Encyclopedia-- From Angel Deveraux through Belisarius to Zachary Banfeld...
WC Nexus forum, the Moonbase Tycho of WC fans.
-
- Site Administrator
- Posts: 1089
- Joined: Thu Jan 02, 2003 10:07 am
- Contact:
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
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
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
-
- Elite
- Posts: 8014
- Joined: Fri Sep 05, 2003 4:03 am
- Location: Montreal
- Contact:
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.
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.
Latest version of Cinemut Opaque
Latest version of LaGrande noodleworks (scroll down).
An evolving La Grande How-To...
The non-working, but latest, CineMut test_bike
PU (Privateer: Parallel Universe's Home). WC or Privateer Drayman for you?
WCpedia --The Wing Commander Encyclopedia-- From Angel Deveraux through Belisarius to Zachary Banfeld...
WC Nexus forum, the Moonbase Tycho of WC fans.
Latest version of LaGrande noodleworks (scroll down).
An evolving La Grande How-To...
The non-working, but latest, CineMut test_bike
PU (Privateer: Parallel Universe's Home). WC or Privateer Drayman for you?
WCpedia --The Wing Commander Encyclopedia-- From Angel Deveraux through Belisarius to Zachary Banfeld...
WC Nexus forum, the Moonbase Tycho of WC fans.
-
- Developer
- Posts: 1831
- Joined: Mon Mar 07, 2005 10:33 pm
- Contact:
-
- Elite
- Posts: 8014
- Joined: Fri Sep 05, 2003 4:03 am
- Location: Montreal
- Contact:
Mine too...
Latest version of Cinemut Opaque
Latest version of LaGrande noodleworks (scroll down).
An evolving La Grande How-To...
The non-working, but latest, CineMut test_bike
PU (Privateer: Parallel Universe's Home). WC or Privateer Drayman for you?
WCpedia --The Wing Commander Encyclopedia-- From Angel Deveraux through Belisarius to Zachary Banfeld...
WC Nexus forum, the Moonbase Tycho of WC fans.
Latest version of LaGrande noodleworks (scroll down).
An evolving La Grande How-To...
The non-working, but latest, CineMut test_bike
PU (Privateer: Parallel Universe's Home). WC or Privateer Drayman for you?
WCpedia --The Wing Commander Encyclopedia-- From Angel Deveraux through Belisarius to Zachary Banfeld...
WC Nexus forum, the Moonbase Tycho of WC fans.
-
- Site Administrator
- Posts: 1089
- Joined: Thu Jan 02, 2003 10:07 am
- Contact:
But that violates my rule. Cram everything in there on the first attempt. Try to get it to work. Fail. Then give up.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.
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
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
-
- Elite
- Posts: 8014
- Joined: Fri Sep 05, 2003 4:03 am
- Location: Montreal
- Contact:
Eventually I meant, not now.
Anyways, soon as you can give me a single table file I can try and read it.
Anyways, soon as you can give me a single table file I can try and read it.
Latest version of Cinemut Opaque
Latest version of LaGrande noodleworks (scroll down).
An evolving La Grande How-To...
The non-working, but latest, CineMut test_bike
PU (Privateer: Parallel Universe's Home). WC or Privateer Drayman for you?
WCpedia --The Wing Commander Encyclopedia-- From Angel Deveraux through Belisarius to Zachary Banfeld...
WC Nexus forum, the Moonbase Tycho of WC fans.
Latest version of LaGrande noodleworks (scroll down).
An evolving La Grande How-To...
The non-working, but latest, CineMut test_bike
PU (Privateer: Parallel Universe's Home). WC or Privateer Drayman for you?
WCpedia --The Wing Commander Encyclopedia-- From Angel Deveraux through Belisarius to Zachary Banfeld...
WC Nexus forum, the Moonbase Tycho of WC fans.
-
- Site Administrator
- Posts: 1089
- Joined: Thu Jan 02, 2003 10:07 am
- Contact:
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
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
-
- Elite
- Posts: 7243
- Joined: Mon Apr 18, 2005 2:40 pm
- Location: LS87, Buenos Aires, República Argentina
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).
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).
-
- Elite
- Posts: 7243
- Joined: Mon Apr 18, 2005 2:40 pm
- Location: LS87, Buenos Aires, República Argentina
-
- Elite
- Posts: 8014
- Joined: Fri Sep 05, 2003 4:03 am
- Location: Montreal
- Contact:
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.)
(Thanks, mkruer, I'll play with it in the morning.)
Latest version of Cinemut Opaque
Latest version of LaGrande noodleworks (scroll down).
An evolving La Grande How-To...
The non-working, but latest, CineMut test_bike
PU (Privateer: Parallel Universe's Home). WC or Privateer Drayman for you?
WCpedia --The Wing Commander Encyclopedia-- From Angel Deveraux through Belisarius to Zachary Banfeld...
WC Nexus forum, the Moonbase Tycho of WC fans.
Latest version of LaGrande noodleworks (scroll down).
An evolving La Grande How-To...
The non-working, but latest, CineMut test_bike
PU (Privateer: Parallel Universe's Home). WC or Privateer Drayman for you?
WCpedia --The Wing Commander Encyclopedia-- From Angel Deveraux through Belisarius to Zachary Banfeld...
WC Nexus forum, the Moonbase Tycho of WC fans.
-
- Site Administrator
- Posts: 1089
- Joined: Thu Jan 02, 2003 10:07 am
- Contact:
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.
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
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
-
- Site Administrator
- Posts: 1089
- Joined: Thu Jan 02, 2003 10:07 am
- Contact:
@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?
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
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
-
- Fearless Venturer
- Posts: 610
- Joined: Wed Mar 23, 2005 6:42 pm
- Location: Northern Illinois
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.
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.
I've kicked the MMO habit for now, but if I maintain enough money for an EVE-Online subscription, I'll be gone again.
-
- Elite
- Posts: 8014
- Joined: Fri Sep 05, 2003 4:03 am
- Location: Montreal
- Contact:
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...
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...
Latest version of Cinemut Opaque
Latest version of LaGrande noodleworks (scroll down).
An evolving La Grande How-To...
The non-working, but latest, CineMut test_bike
PU (Privateer: Parallel Universe's Home). WC or Privateer Drayman for you?
WCpedia --The Wing Commander Encyclopedia-- From Angel Deveraux through Belisarius to Zachary Banfeld...
WC Nexus forum, the Moonbase Tycho of WC fans.
Latest version of LaGrande noodleworks (scroll down).
An evolving La Grande How-To...
The non-working, but latest, CineMut test_bike
PU (Privateer: Parallel Universe's Home). WC or Privateer Drayman for you?
WCpedia --The Wing Commander Encyclopedia-- From Angel Deveraux through Belisarius to Zachary Banfeld...
WC Nexus forum, the Moonbase Tycho of WC fans.
-
- Explorer
- Posts: 10
- Joined: Wed Jul 20, 2005 5:23 pm
Or...
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.
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.
-
- Site Administrator
- Posts: 1089
- Joined: Thu Jan 02, 2003 10:07 am
- Contact:
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
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
-
- Explorer
- Posts: 10
- Joined: Wed Jul 20, 2005 5:23 pm
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.
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.
-
- Fearless Venturer
- Posts: 610
- Joined: Wed Mar 23, 2005 6:42 pm
- Location: Northern Illinois
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:
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.
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:
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.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).
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.
I've kicked the MMO habit for now, but if I maintain enough money for an EVE-Online subscription, I'll be gone again.