The Wheel - Status

[**DONOTDELETE**]

The Wheel D2 SQL testing server is up and running!
The website is up as well (link below signature pic).

It is not meant as anything too serious for now,
I am just exploring the capabilities of php and SQL.
(and my connection)

There is a working php SQL webmap, battles list
and beginnings of a shipyard. (RW, IRC and Forums too!)
All of this info is tentative and may be changed
for campaigns.

I am still working out how to edit the live SQL database
but once I do, I hope to produce a working shipyard
and player roster(?)...

I will also transfer account and map info from the old flatfile
If I can manage it. It may be some time before I can return to
the flatfile with the possibility of SQL mission testing, but I
will do so before the next campaign if I cannot transfer
the account info.

More to come..., comments are welcome...  

[Cleaven]

Could you tell at this point if it is possible to use the battle report information to give a greater DV shift for PvP battles as opposed to AI battles?
Each time I've mentioned this it was as a "wouldn't it be nice" but now it looks like it can actually be done.    

[FPF_TraceyG]

Quote:
Could you tell at this point if it is possible to use the battle report information to give a greater DV shift for PvP battles as opposed to AI battles?
Each time I've mentioned this it was as a "wouldn't it be nice" but now it looks like it can actually be done.    

Indeed, that can be done. All that would be required is to change the hex DV manually through an SQL query (or perhaps even with a php script if its possible). This could be done along with any other non-standard misssion results that you wish to send to the databse via the method previously discussed.

[Cleaven]

Excellent!

Now what about consumables in the spacedocks. Something that I've also put on my wish list is variable prices of consumables, ie cheaper drones for the Kzinti. And also medium and fast drones for the Kzinti before everybody else. I was thinking that to do this all drones would have to be made available in year zero, but the ships would be swept clean of any drones they weren't supposed to have. Same goes for spares, you can buy 30 for your frigate if you want to, but 24 of them will be deleted by the time you get into battle, leaving you with the six you should have.  
 

[Karnak]

Hey, your website is pretty cool, Rajnsaj.  Great job.  

[3dot14]

To repeat my questio in the breakground thread.

CAn teh SQL database be used to track player movement? (like SFC3's hex map?)

[Fluf]

Great work bro.  Remember, you KAT/KOTH brothers are here to help.  Post any testing or anything else you need in the KOTH forums, and we will be glad to help.  We are getting so bored with SFC3 and need to get going with some viable D2 options.  You are headed in the right directions.

Nice website!

[**DONOTDELETE**]

Thanks Karnak!

3dot14, I have added a rudimentary roster
that includes current player position (not
sorted or formatted yet).

I have also changed the battles list to reference the
battleparticipants table so that it now reports who
played who, where, when and with what result.
(Only the last 100 battles, can also display Victory Level,
PP Awards and Host...  needs formatting work)

Cleaven, (always raising the bar...)
once I manage to edit the databse from a php script,
I could charge any price for any ship or any part
purchased with a script from a webpage.
An automated script that populates the shipyards
and polices CnC could also modify ship loadouts.
BUT, that is some time away, I have yet to sucessfully
update the database from a script.
DarkElf has made live edits to the database, and
once I get a chance to consult with him, I should be
able to do the same from a php script.

Fluf, thanks for the offer to help!
Great to have the support of the KAT/KOTH fleets.

[3dot14]

WOW~!

I am speechless...

Excellent work.

If anything an amatuer web-developer (that's me   but I don't know php, did a bit asp back when...) can help. let me know!

[Cleaven]

I consider the variable prices and early drone release for Kzin a wish list still. The last thing I want to happen is silly ideas and add-ons bog down the software so that only five people can play before the server croaks. BASICS FIRST!  

[**DONOTDELETE**]

Excellent points Cleaven, you have the right idea,

The Wheel is intended to be a small scale server
(due to connection and hardware...).

It is not a good platform to test large player
numbers on the SQL database. We will have
to wait for the SFC2.Net server to be set up at
XenoCorp for that. (Or continue SQL stress tests
on DarkElf's setup - but numbers have been low...)
...in the meantime I can test SQL missions and
explore the possibilities of SQL and php.

The most important test of SQL is high player
numbers, of course. Until we have the players or hardware
and connection available there is not much we can do.
(It is impossible to resist playing with SQL in the meantime...)

The Wheel connection and hardware would poop
out almost as quickly on the flatfile as on SQL
regardless. (Thus the low max player #)
So I have decided to make the most of it,
and keep myself occupied till the next big
campaign.

Glad you like it 3dot14! (It should get better too...)
It's mostly all php, but if I get stuck anywhere I'll definitely
post for help!    

[UDF_Intruder]

Rajnsaj,
Your server and Web Site is most impressive.
I use perl to connect with my SQL server, but like how you have fed your web site with php.

Last night I did some missions in a base hex and noticed that the type of base still changes depending on your composition.
I entered the base hex with a single ship and the base was a Battle Station.
After exiting, adding 2 wingmen and returning, the base was now a Star Base with 3 Heavy Cruisers.

I guess that part of the script never changed, eh?

[**DONOTDELETE**]

Gosh, thanks!  

Funny you should mention perl...
I was having a heck of a time trying to find a function in php
to convert a little-endian 4 byte longint to a decimal value and
when I read in the php onlne documentation comments about
the superior function of the perl pack and unpack functions I
considered moving over to perl. I got stubborn though and
figured out a way to make php do it.

This was to calculate the stardate from the currenttime blob
in the database. Now that I have figured it out though, the
value after the decimal point on my calculated stardate does
not match that of the stardate on the campaign map. The
year does match however.
(Not the stardate at the serverlist - that one is always wrong)

I guess the value after the decimal point on the campaign map
is not a simple fraction of the year that has passed ...
I searched on the web and found nothing conclusive on
calculating star trek stardates (only that the TOS dates are not
consistent with TNG dates). Anybody know how the stardate
is calculated exactly in SFC?
I am calculating it as: stardate = (TurnNo / TurnsPerYear) + BaseYear

I have added considerably to the information presented from
the SQL D2 database on my site in the last week (economy, news,
player count and player ships on the roster, campaign info).
But I am slowing down as I get to the data that is stored in blobs in the SQL
database (flatfile leftovers?). I want to tackle the politicaltension
matrix next,  and I am saving what I see as the most difficult
for last - the full shipyards with damage and stores info.
Currently, the site only displays info from the database - no
updates yet. Ideally I want to create an external shipyard that
will update all the appropriate fields, blob chunks and db
object count to allow for external manipulation of the shipyards.
(as discussed above...)

After making all this progress with the php and SQL I regret
to say that I don't think I'll be returning to the flatfile database
as promised. I will do my best to figure out how to update
the new db with old account info though.

That is kind of where I am stuck though, updating the database
properly. (Like how to assign ships etc...) I could use a jumpstart
on that if anyone has the time to explain.

I saw your battle in 19,10 on the battles list, Intruder. The one
against four AI - I felt for ya .    (Ugh, nebula hex at that too!)

Note: I added the campaign difficulty from the db to the Campaigns page
(currently set to Commodore). I'm not sure but maybe this affects what
type of base appears in missions? I know it makes for nasty ambush missions!
(Future servers will likely use new missions from ED and clintk which may
change this...)

There is also a java project in the works which has a lot more potential that
I want to contibute to, but that is being slowed down by our lack of knowledge of
the blob structures. We have figured some of them out, but
any help would be greatly appreciated (no need to re-invent the wheel,
oops, no pun intended...).

I'm having a lot of fun working on this, it's nice to see others appreciate it too!
(I have to fly more though, my piloting skills are getting a little rusty with
all this time spent on db analysis!)

   

[UDF_Intruder]

When you say "I could use a jumpstart in updating the DB", do you mean porting flatfile data to the SQL table(s) or merely altering the SQL table data?

As for the Base Hex question, I think the scripts create a base dependant on what's attacking. Just like it adds AI ships to scenarios that otherwise may be unbalanced.

I don't know where you stand on scripts creating AI's, but I think that scripts should match available ships and/or bases/planets only, no creating.

DarkElf and Tracy said they had solved the function for embeding SQL statements in a script.
Imagine if a script where to draw it's ships/bases/planets from a remote DB, instead of creating anything on it's own.
There would be only 1 script. Receiving the map, conditions, ships, etc from the DB, then reporting the results back to the DB afterwards.

On the Serverside, the map and all supporting web pages, what-have-you, would be updated accordingly.
If a hex has a Battle Station in it and another player happens to be there, then that's all the script uses to set up the scenario.
No additional ships if you happen to have 3 Battleships, or finding the BattleStation is now a Star Base.
When you destroy the Battle Station (and watch the other guy run like hell), it really will be gone.

I had written some scripts for myself for EaW. Like Magnums scripts, they could read and write config files.
Now I have SQL tables to which I can store ship data and other object data.
The link I need is going from scripts to SQL statements to start developing this concept.

Was that a sincere-enough plea for enlightenment?  <grin>

[**DONOTDELETE**]

I mean merely altering the SQL table data, which is already no problem for
the normal fields, but the blob fields pose a problem as I do not know their
structure. I cannot update fields that I don't know the structure of.
(such as the OpenShipBids and ShipCacheVector in the servcharacter table
or the Damage and Stores blobs in the ship table - which is what I think I would need
to edit the db to give players back the ships they had on the flatfile) PP, glicko and
map edits are no problem though. (So in answer to your question, both
actually, same barrier is present - blob structure)

I have figured out the structures of only a few blobs. Actually updating the
database once I have this information should be trivial.

I found the following quite helpful wrt to SQL:

DbVisualizer

phpMyAdmin

SQL tutorial

expertsXechange  MySQL Area

JDBC documentation

PHP: MySQL Functions

I'm not really familiar with mission scripting myself (never got a copy of VC++ 6).
I once tried to create a script without the API in MS VC++ 5 but I really didn't know
enough C to get very far. I do understand the principles of querying/updating the SQL db
from a mission script though. I thought scripts had to create AI's - what if there is no
human opponent?

Hope the links help in your quest for SQL enlightenment - I have yet to read all
the pertinent material there myself.
   

[UDF_Intruder]

I see what you mean. I remember...someone had figured the values before.
It's been a long time since I looked at the server database, but can you send a copy of the DB?
I don't know if I can do anything, but I'd be willing to try.

As for AI's, if you populate the ship table with ships that aren't players, but AI's......
then place them in positions on the map, they'd be a stable presence in the dynaverse.
They could be made to move about, and except for being AI's, would exist just like players.

If scripts never fabricated ships, you could actually create stable orders of battle.

Just a thought.

[txtanker]

The Blob stuff is the only big thing holding us up on finishing the Java project. When it is done it will give you the power of the "Q". The ability to see the universe and also to change it, on the fly.

Maybe I should change my name to Q.

Please any help with this Blob problem would be great.

TxTanker  

[**DONOTDELETE**]

"Q" - I like it
I just sent my proposed missionlist structure to you.

Thanks for the offer to help, Intruder. I'll send you a dump
of the SQL db (850KB - stripped of e-mails and IP addresses
for privacy of users). Just mail any proposed blob structures
to me and txtanker ("Q").

I was chatting with DarkElf on ICQ last night about
editing the ship and biditem tables and I think I have what
I need now to make a minimally functional php shipyard.
(just need the blobs for full function...) - Thanks DarkElf!

This is shaping up nicely...    

[UDF_Intruder]

I think I've got the Political Matrix Blob figured out.

I'll E-Mail you the details.

On a hunch, if you convert (hex) '9001000014AEC73E' to decimal (I don't have my Hex calculator on this machine),
does it equal the starting value for most StartingTension entries?

[txtanker]

This is what is comes out to be:
10376575016785332030  

[UDF_Intruder]

OK, so much for the simple answer.

Can someone E-Mail me his MetaMap.gf file?

[**DONOTDELETE**]

Sure, its on the way... (hex editor too...)
Note that the windows calculator will do some hex->dec conversions too!

I'm just looking at that now too. That repeating pattern you observed
likely has somthing to do with it... I think one of the values we are interested in
is stored in the byte immediately after each "14 AE C7 3E" sequence.

This one is tricky!
 

Arrgh! peerchat.gamesy.com is down again - most annoying,
back to local chat server (has some issues...)

[UDF_Intruder]

OK, I just sent you the results. Got it beat!

Even though EaW has 8 playable races, 1 non-player race and monsters, the tension matrix has 17 tables!
Only the first 8 have any data so Orions and Monsters don't seem to have Tension Values.

The remaining tables may have been for expansion.

[**DONOTDELETE**]

To cool, that's great!

I'm dying to make a politics page with the
info, but I have to be up early tomorrow.
I hope to get that done on the weekend.

I'm now collecting Stores blobs in various states
of supply to work out their structure for the
shipyard.  

[Cleaven]

I wonder just when the two layer OP map was thought up!

Quote:
OK, I just sent you the results. Got it beat!

Even though EaW has 8 playable races, 1 non-player race and monsters, the tension matrix has 17 tables!
Only the first 8 have any data so Orions and Monsters don't seem to have Tension Values.

The remaining tables may have been for expansion.  

 

[DarkElf]

17 tables you say?

I wonder if there being 17 ways for the political control of a Hex to be under has any coincidence?

Could you email me thre results as well rajinsaj?  This sounds most interesting...

[**DONOTDELETE**]

No problem,  I just forwarded the
political tension matrix structure to you.

I'm thinking we should summarize all
this in a file to post somewhere when
we're done so that future D2 projects
will have a handy reference.

It sure would've helped me!  

[UDF_Intruder]

Guys,
I have the Ships Damage Blob almost solved.

It's a single table of 25 internal components. Each component has an Original and Destroyed value.
Probably used to figure Damage Percentage.
Power Systems are first, followed by Non-Weapons, and then Weapons.

They pretty much coincide with the shiplist except for 2 values that I can't find, and 2 values from the shiplist that don't seem to have been included. I am using the standard EaW 2036 shiplist.

Further study will be required to identify 100% of the component names  because the ship diagram I used didn't have all possible entries and entries with similar values can't be uniquely ID'd without something to set it apart.

[**DONOTDELETE**]

I have decoded the political tension values
from the matrix blob, but I treated each row
of each table as three 4 byte longints.
(instead of 1,5, and 6 Hex digit groups -
should result in the same values)

Intruder, could you take a look at the "Politics" page
and tell me if those are the same kind of numbers
you observed in the copy of the matrix blob you decoded?

I have yet to calculate the actual alliances according to: (?)

AllyRatio=0.25  //This number determines what percentage of races are allies. Calculated vs most hated enemy
NeutralRatio =0.5 //This number determines what percentage of races are nuetral. Calculated vs most hated enemy

and

DistanceWeight=1.0
TensionWeight=1.5

... and then relate these to the text used in game to describe them ("We are at war with...").

I am looking at implementing SSL for the shipyards and supply pages
to allow for secure logins to purchase, repair or supply ships.
It will take a little work and I'm not really sure I can yet...

Great work on the Damage blob, we've almost got what we need!

[UDF_Intruder]

Your politics page is confusing.

In the first table (Fed), only the Romulan and Lyran Current figures are correct.
The Original Values for all 8 Races are right, but the others not mentioned above have values way off.

In the Fed Table, the ISC actually have a current value of 63, but the listed value is huge.

I can't guess yet why the values above could get as large as they are.
Since other values seem to be more "normal", I don't think it's your code.

As far as interpreting the numbers, you'll probably have to do observations to determine trigger values for the different
text descriptions.
Since it's obvious that 0 means "close friends",  go from there. Print a matrix, then click hexes and compare the values to
the descriptions.
As values increase, changes will give away the triggers.

How does that sound?