Breaking new ground with Mission Scripting and SQL

[FireSoul]

It seems alright.. If there's some kind of unique data tag on both ends that can be used, that's perfect for authentication. That data tag would only be created by a D2 server starting a game? Perfect.

.. it would indeed be a tunnelling protocol that would make sense... and giving each player his/her own password to play makes complete sense. At this point, a separate 'modular' file used for authentication would be awesome.


What's the server-end platform for this "app"?
-- Luc

[LonnyE]

Alot of problems might be avoided by players only being permitted to act on staging tables.  Only permit players to act on a mission results, location tables, etc.  Periodically (trial and error to determine frequency) update main db tables with info from staging tables.  Server admins and other trusted folk could be given a different front-end.

Parse all incoming queries to ensure that users are only querying against the staging tables.  If some a** still somehow fubars the staging tables then it is no great tragedy as only info from the the last update of the main db tables is lost.

This may also allow larger numbers of players to play as there will be fewer (but of course larger) transactions.  I think this ok in regards to enemy player locations as the map becomes a reflection of last known enemy location (a bit of fog of war).

I dont know much about MySQL.  We use Oracle and Cold Fusion where I work.  If I can be helpful let me know.


Lonny  

[Karnak]

I agree with LonnyE.  The more functionality you can keep on the back-end SQL server the better.  Writing a middle-ware server object creates more system complexity that may not be needed, not to mention a lot more potential bugs and gnashing of teeth. You can have a polling stored procedure call written up to update the main tables from the player "scratch" tables.

[clintk]

Nothing like a bit of brainstorming !

Does anyone know why we're using MySQL ?

[Cleaven]

A word of warning about robustness, from a bugs and player crashing point of view. Things still have to work even though one player may detonate as he crosses the line, or AI gets his ship etc. That became my biggest problem with advocating destroyable starbases. Even though they are made big and tough, a bug could make them go pop, and conversely a player who deserves to see a result (eg starbase destroyed) is robbed of it by a bug. Just don't get too complicated to begin with otherwise it may fall in a heap.    

[SPQR Renegade001]

While were breaking all this new ground with SQL, is some sort of external shipyard in the cards?
If we had a vanilla server set-up, with all the evil playtoys purchased outside of the game, we could eliminate the need for remembering and policing all those nasty C&C rules, and implement some form of OoB.  

[**DONOTDELETE**]

As FireSoul has indicated we cannot rely on the MySQL server's
own security, so solutions that rely on it are unacceptable.
I think it would be good to secure the database as well as possible.
If it is done using an intermediate server application then it should
work perfectly and be tested to death.  I think the threat of  the db being
hacked outweighs the additional complications concerns.
Mission scripts that are not server specific would certainly be a good
feature of this approach.

WRT using the mission start time as an authentication
component - as long as the mission start time is definitely
the same one submitted to the db by the serverplatform

Hopefully the same application could be used to provide data to
a webmap java-applet. I am helping a friend to write such an
applet and I believe he has accounted for this by developing
a java servlet to do exactly this. (i.e.: The same job as the
"reporting server" you propose, but I'm not 100% sure yet,
waiting on reply...)

It would be very helpful if anyone knows for sure the
relational structure of the databse to help expedite this
process. I have been working on extracting the relationships
myself, but it would be nice to know for sure...

clintk, with regard to "why are we using MySQL?"
- It's free! (so's PostgreSQL too, I know...) I've never
tested it on another db server, but I seem to recall
reading of someone using the MS-SQL server.

Cleaven, excellent advice. Indeed, Occam's Razor
should be applied!

Renegade, glad you mentioned the shipyards.
An external shipyard shouldn't be too hard
but I can see a number of complications.
I think that it would be more effective to produce an
application to automate the policing of CnC and
population of the shipyards. (any volunteers? )

[FPF_TraceyG]

Well, it need not necessarily be the start time, but anything really that is unique to that mission will do that is passed to or accessible by the mission script and stored somewhere in the database so it can be used for authentication.  

[Kel]

Quote:
Well, it need not necessarily be the start time, but anything really that is unique to that mission will do that is passed to or accessible by the mission script and stored somewhere in the database so it can be used for authentication.  

Tracey,

I'm a little confused on your proposed architecture.  You are suggesting a client-side program that resides on each players PC.  This program will pass along info to a server side program regarding mission results.  How does your client-side program get the results to send? Do you intend to create special missions (which again reside on the player PC) that pass this info to the client-side program?  Am I correct so far?

Once on the server side, your dedicated program can report to the database all kinds of 'special' mission results as you have mentioned above.  

If this is all true, then how do you prevent the 'real' D2 server program from reporting traditional mission results which may or may not conflict with your special results?  

If I'm still on the right track so far...is one possibility to effect hex DV's differently if a player is in PvP vs an AI only misison?

Great work, by the way.  

I am available to help test, design or code reviews.  If you need any help in these aeras, let me know.  I graduated with honors from the Gorn Academy of Advanced Computing !

GDA-Kel
Gorn Dragon Alliance

[FPF_TraceyG]

Actually there would be no need for a client side program. The mission script is doing the results reporting itself. The results it reports are in addition to those reported by the game. They will only only conflict if you havent designed the mission with that in mind.
The aim is to add in some extra features that SQL can offer by modifying the SFC2 server database directly based upon mission results that currently, the Taldren API does not offer. For example, there is no function for instance that allows you to change the Economy Value of a hex within a mission script. However, by accessing the DB with SQL, you can. The update to the DV of a hex is not done with a direct call to a function or setting some parameter either (as far as I can tell). It relies upon the Victory Conditions and whether or not the player won in a mission. As far as I can tell, a 'win' result in an enemy hex must call some function within the game itself (which we dont have the source code for) that changes the DV. I still havent figured out yet why the DV dosnt update in a neutral hex whilst playing a coop mission. Perhaps it just simply doesnt know which way to shift the DV.
In effect, making SQL queries directly to the DB bypasses the API and allows us to do things we wouldnt otherwise be able to do. To be honest, this wouldnt need to be necessary at all if we had the source code for the game, we could just add in any function we liked. Who knows... maybe one day...  

[UDF_Intruder]

Just out of curiosity, has anyone checked the transaction log to see what and/or how ServerPlatform manages the database?

Excuse me if that was a dumb question, but if you could document what does and does not get altered, that would help figure out what needs to be done.

I haven't gotten around to setting up a SFC Server using SQL, but would love to see the structure.

[**DONOTDELETE**]

Quote:
Just out of curiosity, has anyone checked the transaction log to see what and/or how ServerPlatform manages the database?

Excuse me if that was a dumb question, but if you could document what does and does not get altered, that would help figure out what needs to be done.

I haven't gotten around to setting up a SFC Server using SQL, but would love to see the structure.

Duh, I've been trying to figure out the db structure, and I forgot all about tracing the ODBC driver calls.  
Just enabled tracing on my test server now.

That may help with the details of the db that I havent figured out yet... as well as how to implement
db security. I'll check it out once the log collects enough info...

Thanks for the input!

P.S. My tentative structure of the database can be found here:
tentative db structure
If there are any glaring errors or omissions please let me know.
(aside: so far I have been unable to capture any data from the 'battlesrunning' table but have caught data
in the 'preparedmissions' table...)

 

[Tao]

Didn't realize that was your test server. Tried just about every mission, they all seemed to work well. No PvP, but maybe later when more people are on. Friendly space (0809); monster, quite a few patrol, convoy escort. Didn't get a scan mission. Enemy space(0909); patrols, convoy raid, no scan missions, base assault (BATS). Neutral space; not sure which hex it was, but just south of the above hexes, patrol, convoy raid.
All missions were in a D7 variant or D5K.    

[**DONOTDELETE**]

Had a quick look at the ODBC trace - looks quite arcane to me - not sure it will be much help.
(EDIT: I think I was using the wrong dll to trace with, will try again...)

I went looking for MySQL's transaction log but wasn't sure where to find it...
I checked the manual (not lost! ) and found that:

"MySQL supports two different kinds of tables: transaction-safe tables (InnoDB
and BDB) and not transaction-safe tables (HEAP, ISAM, MERGE, and MyISAM)."

Since all the tables are of the MyISAM type, I don't think a transaction log is produced.
If present, they should be in "/mysql/data" but I only find the error log there.

Please correct me if I have misinterpreted this or missed the obvious.

P.S. Thanks for the report Tao!

[FPF_TraceyG]

Hmmm, thats interesting... would you be interested in doing some mission testing, rajnsaj?

[**DONOTDELETE**]

Sure, I think I could manage testing a few mission scripts.

I did promise some users I would restore the flatfile database
they were working with.  I think I can put that on hold for this.
Hmmm, maybe I could just transfer everybody's account to
the SQL db? (could be tricky...)

In either case, send them along when ready and I can give them
a whirl.  

[FPF_TraceyG]

Do you have MSN or ICQ? What I really need to do is to be able to test out a mission (which currently I havent been able to do on the D2), see if it actually works first, fix any major problems with it, recompile it, and get it to a point where it can be left on a server for exhaustive testing. So coordinating with you intitially would be helpful. Any chance we can arrange something like that?

[**DONOTDELETE**]

Just added ICQ # to my profile...

Sure, I figured as much would be necessary to
test a few missions.

I'm kind of busy this weekend, I am expecting
visitors tomorrow and the first Ottawa / Montreal SFC
meeting is on Saturday!

We could get started next week (Monday?).
Just check for me on ICQ to work out the details...  

[Goose]

Ummm, Tracey...

I sent you an E-mail a little while ago about mission scripting, have you had chance to read it yet?

If so I now have a few more questions about the database in general.

Please answer...

[UDF_Intruder]

Hmmm...MyISAM tables are the default type and they don't support transaction tables.  Too bad.

[FPF_TraceyG]

Quote:
Ummm, Tracey...

I sent you an E-mail a little while ago about mission scripting, have you had chance to read it yet?

If so I now have a few more questions about the database in general.

Please answer...  

My apologies, I've been a bit slack with my email lately, and I promise to reply soon...    

[FPF_TraceyG]

Rajnsaj, I've added you to my ICQ contact list, thanks.
M'Ress, I've replied to your email as well...

[FireSoul]

Good thing we're not using MS SQL:

http://securityresponse.symantec.com/avcenter/venc/data/w32.sqlexp.worm.html

-- Luc
FireSoul

[Cleaven]

The bad thing is we are locked to GSA which did go down.  

[FPF_TraceyG]

Quote:
Good thing we're not using MS SQL:

http://securityresponse.symantec.com/avcenter/venc/data/w32.sqlexp.worm.html

-- Luc
FireSoul  

MySQL has a vulnerability issue as well....
http://www.nipc.gov/cybernotes/2003/CyberNotes-2003-01.htm
(scroll down about half way down the page)
Fortunately for us, it's limited to Unix systems only.

[clintk]

The MS SQL Server worm highlighted here simply causes performance to degrade as the SQL Resolution Service sends thousands of packets to other services that run on the same port. Microsoft have published a patch to address this issue.

MySQL has several vulnerabilities, one of which allows a malicious user to obtain unauthorized database access by exploiting a vulnerability in the password authentication mechanism. A truly fundamental flaw !  Three tools have also been created that take advantage of a vulnerability in the check_scramble() function. No patch has yet been published to address this issue

I hope you now understand why I made such a fuss about us exposing the MySQL port on the firewall, or any database server port for that matter.

[**DONOTDELETE**]

Would it be somehow possible to post mission results to
a php script?

I already posted in the meeting thread on Off Topic forum:

"A few things congealed for me after discussions on
Saturday and I was motivated to find the limits of what
I could do with php to display and manipulate the EAW
D2 SQL database.

I managed to create an image based php-SQL webmap,
a battles summary and the beginnings of a real shipyard...?!?!?!!!!
There's still some work to go and there's a few rough edges
but I'm pretty happy with what I've gotten so far.

Php avoids many of the security issues with SQL server
features. (But work on a java solution has begun too...)

See the link below my signature pic..."  

[FPF_TraceyG]

Well done on the website, rajnsaj. I can see quite a lot of work has gone into things there, and very interesting too.

How much control do you have over which ships appear in the shipyards?

[**DONOTDELETE**]

Thanks!  

Currently the php-shipyard is read-only.
Just a dump of the 'ship' table referenced to the
'servcharacter' table... (like the battles list is
a dump of the battles table referenced to the
'servcharacter' table)

I intend to produce adminstrative shipyards
where the whole shipyard can be edited (or by empire),
and a player shipyard where ships can be purchased,
repaired, supplied, traded-in... I am just starting to test
this capability myself with phpMyAdmin.

Currently mullling over how to handle shipyard login.
I might be able to use the phpbb forum login to handle it
or a combination of charactername WONlogon and a
new 'shipyards password'?

Then there is the matter of automating the population of the
shipyards, this will take a little more time. I am thinking it is possible
by leaving a local browser window open running an administrative
php script set to refresh in sync with the turn frequency.
This script could contain other database admin functions too.
(Or maybe a set of "cron" scripts for MySQL?)

I'm working out just what I can do with the database
now.  It seems php has access to the full functionality of MySQL.
MySQL will not accept stored procedures though, i think.
There might be some packet size issues with editing blob fields
from php, but there is a setting to modify if so.
(I have yet to figure out the blob structures of
the Officers, Damage and Stores fields to edit them anyway)

(note: I read recently that there are no explicit 'foreign keys' in
MyISAM type tables. - was late, lost link to info, looking again...)

So, to make a long story short, I think I can have fully
automated control of the shipyards through php.

 

[TOCXOBearslayer]

  Wow... I registared at your site and logged on to the D2 you have running... While the D2 it self was normal... the information available on the site was both amazing and somewhat distrubing....

You can see exactly who attacked what hex.(this is the disturbing part, I don't know if that much info should be availble).. how many folks were involoved, though it seems to always be 1+ how ever many people are actually in the mission.  (no one was on and all my missions showed as having two players.) and lots of other stuff.. maps... shipyards.... sheesh....

All in all.... wow....