Topic: Breaking new ground with Mission Scripting and SQL

[FPF_TraceyG]

From the mission coding laboratories at SFC2.net comes an announcement.

By combining SQL queries with mission scripting, DarkElf and I, today were successfully able to modify the D2 map terrain from directly within a mission script. Out first test was to create a planet, which upon completion of the mission, then appeared on the main campaign map.

Can anyone say "Genesis Device" ?

This opens up a whole new realm of possibilities for mission scripting.

Creating planets out of nebulas. Destroying planets and turning them into asteroid hexes. etc.
Furthermore, not only can the map terrain be changed, but other factors like Econ value as well.  "Sabotage Economic Assets" missions lowering Econ value etc. are all possible.

But here's something that really has potential...
The mission script can also change the location of a player to any hex on the map. Imagine entering a mission and flying into a wormhole, and upon completion of the mission finding yourself on the other side of the map....

The possibilities are limitless..

So where's the catch? Well, acually, there is just one small problem. These mission scripts will only work on a SQL D2  server of course (so single player campaign's are out). So the challenge now is to get the SQL D2 server working and stable for a major online campaign. That part is still a work in progress... the main problem it would appear is server load and gettng it to work with more than 10 people on the server at a time.

On a more general note, the new mission pack (non SQL) for the next major SFC2.net campaign is coming along well and hopefully we'll be putting up a minor campaign very soon to test out our new home at XenoCorps, along with the mission pack and a revised shiplist which may include a lot of ships from OP as well. Its all looking good, so hang in their fellow EAW players.

From the team at SFC2.net.  

[DarkElf]

Ouch...my head hurts.

It took lots of trial and error, and help from Tracey, but we were able to accomplish the task.  Without the help she has given me, this would not be possible...

But we persevered!  (And to think, Im a newbie programmer, LOL)

[Cleaven]

Tell me that you can destroy a base in a neutral hex or hex with DV zero and I will think about putting a cheque in the mail.  

[KATChuutRitt]

You two are absolutely terrific!!!!

The possibilities just boggle the mind,   Convoy raids that affect economy, planets that don't appear on the map unless a suvet mission is done in a particular hex, etc.

The D2 will really feel like a true environment....wow .......what can I say........

Big grin and a bigger salute!!!!

[DarkElf]

Yes, Cleaven, that can be done....entirely possible.

[**DONOTDELETE**]

Well done....

I think I speak for many SFC2 players when I say ...

THANK YOU!

Your continued efforts to improve DV play is outstanding.....It is my firm belief the SFC2 is still coming of age, and will continue to be played by many people.....

Also....a development such as this need to be bumped up the line to the beast or Dave ferrel or even Erik...as this is the very first time I've heard this was possible....and even Taldren might be interested in this technique.....

I doubt they would re-open EAW patching...but there is still an OP patch being worked on....and maybe there are some code  tweaks that would aid in this new development being used.....clearly.....there is a stablity problem with high player numbers and SQL servers that should be addressed .....

[Cleaven]

EAW itself would not need more patching, just the server kit and those aspects that deal with the stabilty and function of the SQL database. Plus writing new mission scripts to take advantage of it.
(And make a Linux version too.)

Also have to add the caveat that a basic SFC2.net flatfile db compatible mission list should be a priority.  

[FireSoul]

can you tell me what kind of SQL SFC server use?

... I can see how this was done, actually..
.. as part of the mission success, a change to the DB was done with the script. Thus the SQL server saw that a planet was then in that hex, etc.

.. interesting work gentlemen.. interesting indeed.
..hey.. idea.. a monster spawning planet?

-- Luc

[Corbomite]

I would say something, but I'm speechless.

[clintk]

Are we going to expose the SQL Database Server port on Xenocorp's firewall ?

[FPF_TraceyG]

At this point in time, no, the XenoCorps server will not be running a SQL campaign because of security issues. Encrypting the password seems to be the best solution, however. How might this be done? Well, I could tell you... but then, I'd have to kill you...  

[NuclearWessels]

OUTSTANDING!!!!!!!!!!!!!!!!!!!!!!!!!!

That is the best of news for online campaigns (one the SQL side is reliable),
and would address many of the huge headaches caused by the API to date!

Well done!


dave
 

[**DONOTDELETE**]

Wow, great stuff!  

I'm a little confused though...

You don't need to open the database to the internet to
run the SQL server - only the serverplatform needs to
see the database right? (either root access on the same
machine or another machine on the same LAN behind
a gateway?)

You are discussing opening the db to allow client
side scripts to read / modify the db?

You could always limit the scripts to a single server
by using a password protected connection in the
compiled script? (relatively safe...) Or does an
actual sytem DSN need to be on the client machine
for this to work? (php scripts and java applets can connect
without a MySQL ODBC driver system DSN...)

Another issue with the SQL db, is safely opening
it to a webmap applet. I think this would best be done
by creating a login for the applet with read-only
privileges. This would of course require the db to
be open to the internet and might allow brute-force
password attacks on any logins with full privileges.
(I would hope this kind of attack would be unlikely,
and would result in DoS more than anything...)

I think it can be done safely. I'm sure anyone who plays
on SFC2.Net servers would not try to hack in, it's just
a matter of keeping all those other yahoos out there
on the internet out of it!

We will certainly need to securely open the SQL db to the
internet to get all the potential benefits SQL has to offer.

Regarding stability (processor load)  with high player numbers, I still
suspect that the best performance may be obtained by putting the
MySQL db on another machine on the same LAN (100Mbit or better)
with the serverplatform.

Sorry for rambling but this really got me thinking...
(A little more detail on how this was done might be nice.)

P.S. FireSoul, the EAW serverplatform uses a MySQL db
and connects to it using a MySQL ODBC driver system DSN.

 

[Skawpya]

So when will the server go public again? and if it does, whats to be tested for now?

[Mog]

I am very excited by this. Marvellous work

[FireSoul]

Quote:
P.S. FireSoul, the EAW serverplatform uses a MySQL db
and connects to it using a MySQL ODBC driver system DSN.

Good. Thank you. I have plenty of MySQL experience, and am familiar with MyODBC, how to install it and how to use it.
.. you wouldn't happen to have a dump of the SQL tables and its columns handy, would you?

-- Luc

[DarkElf]

Clint, just for the record, I did throw my original idea out the window.

Instead of using your IP Address (which was a major security risk) we use your Metaverse ID instead.  More than likely they are using the default port for their MySQL server, and if there are more security issues that need to be addressed, we will resolve them.

In this case, not only the ServerPlatform would need a connection to the server but the Client who is running the mission as well.  The connection only lasts a second and connection is terminated immediately when the query is finished.

More details on how this was done can be revealed later.

[clintk]

I'm just a little concerned that we want to open a TCP/IP connection to our database server from a client PC using the internet. This means we will have to expose the database server port on the firewall, one of the big no-no's when designing a secure system.

Might I suggest that we design a secure web service that runs on the database server and that this is the only component that opens a connection to the database. The database server therefore would not have to be exposed on the firewall, negating the security hole. Not only could we easily upgrade this component, we also wouldn't have to keep upgrading the scripts at the same time. This would save on bandwidth as hosting the scripts for downloading would not be an issue.

The web service could expose functions that would facilitate updates and querying, the client scripts would simply have to open a http (https) connection and exchange XML via SOAP. This could easily be wrappered in a component that resided on the client, reducing the size of the client scripts and enabling us to develop the the component in .NET or COM.

I hope this all makes sense, but I'd hate to see a campaign ruined by some 14 year old hacker.

[**DONOTDELETE**]

Quote:

Quote:
P.S. FireSoul, the EAW serverplatform uses a MySQL db
and connects to it using a MySQL ODBC driver system DSN.

Good. Thank you. I have plenty of MySQL experience, and am familiar with MyODBC, how to install it and how to use it.
.. you wouldn't happen to have a dump of the SQL tables and its columns handy, would you?

-- Luc  

Funny you should mention - I just did that today!
(Just mailed it off to the address in your profile)

clintk - great idea to use a "secure web service" -
http (https) connection and exchange XML via SOAP
sounds tricky though (thinking of Java-Webmap).
A php webmap would run serverside though
so no problem security-wise (I'll stay on it...)

Is there an easier way to lock it down?
(I do like your idea though - sounds efficient too.)
 

[FPF_TraceyG]

That makes perfect sense ClintK and it's an excellant suggestion. It is certainly an option.