forum.webdiplomacy.net

webDip dev coordination forum / public access todo list
It is currently Mon Nov 20, 2017 12:10 am

All times are UTC




Post new topic Reply to topic  [ 4 posts ] 
Author Message
PostPosted: Sun Jun 20, 2010 6:10 am 
Offline

Joined: Wed Jul 29, 2009 10:22 am
Posts: 841
Hi,

curently in my FogOfWar variant you can see units and all territories + 1 territory.
I want to change this, so you can just see +1 terrtory on SC-territoris and units.

But I'm not that good at SQL. This is the curent code, anybody give it a try?
Code:
   if( $turn==-1 )
   {
      // territories + units => +1 field
      $sql = "SELECT b.toTerrID
            FROM wD_Territories t
               INNER JOIN wD_Borders b
                  ON ((t.id=b.fromTerrID  OR t.id=b.toTerrID) AND t.mapID=b.mapID)
            WHERE (t.coast='No' OR t.coast='Parent') AND t.countryID='".$mcountry."' AND t.mapID=".$Variant->mapID." GROUP BY toTerrID";      
   }
   else
   {
      if ($turn == $latestTurn) {
         $sql="SELECT t.id FROM wD_Territories t
               INNER JOIN (SELECT b1.fromTerrID as terrID FROM wD_Borders b1
                  INNER JOIN wD_TerrStatus ts
                     ON (ts.gameID = ".$Game->id." AND ts.countryID=".$mcountry." AND (ts.terrID = b1.fromTerrID OR ts.terrID=b1.toTerrID))
                     WHERE b1.mapID=".$Variant->mapID.") tsb
               INNER JOIN (SELECT b2.fromTerrID as terrID FROM wD_Borders b2
                  INNER JOIN wD_Units ma
                     ON (ma.gameID=".$Game->id." AND ma.countryID=".$mcountry." AND (ma.terrID = b2.fromTerrID OR ma.terrID=b2.toTerrID))
                     WHERE b2.mapID=".$Variant->mapID.") mab
               ON (mab.terrID=t.id OR tsb.terrID=t.id)
            WHERE mapID=".$Variant->mapID." GROUP BY id";
      } else {
         $sql="SELECT t.id FROM wD_Territories t
               INNER JOIN (SELECT b1.fromTerrID as terrID FROM wD_Borders b1
                  INNER JOIN wD_TerrStatusArchive ts
                     ON ( ts.turn=".($turn)." AND ts.gameID = ".$Game->id." AND ts.countryID=".$mcountry." AND (ts.terrID = b1.fromTerrID OR ts.terrID=b1.toTerrID))
                     WHERE b1.mapID=".$Variant->mapID.") tsb
               INNER JOIN (SELECT b2.fromTerrID as terrID FROM wD_Borders b2
                  INNER JOIN wD_MovesArchive ma
                     ON (ma.turn=".($turn+1)." AND ma.gameID=".$Game->id." AND ma.countryID=".$mcountry." AND (ma.terrID = b2.fromTerrID OR ma.terrID=b2.toTerrID))
                     WHERE b2.mapID=".$Variant->mapID.") mab
               ON (mab.terrID=t.id OR tsb.terrID=t.id)
            WHERE mapID=".$Variant->mapID." GROUP BY id";
      }
   }


Oliver


Top
 Profile  
 
PostPosted: Sun Jun 20, 2010 7:19 am 
Offline
Site Admin

Joined: Sat Jun 28, 2008 6:24 am
Posts: 892
In a rather study averse mood, so I'll chime in; fog of war is a damn tricky one with many things to overcome:

- You need to have maps unique to each player, as I'm sure you know, with each player unable to access the maps of any other players, and offhand I'm not sure how I'd do that.
- The same thing applies to the map JSON data, otherwise the tech savvy players will check the JSON files and see where everyone is right away. I don't know how the fog interacts with convoys giving access to coasts which may be beyond the horizon yet still valid, but there may be some tricky issues in the details there
- There needs to be some SQL-fu in map.php, and again also in the JSON data query, which is I think what the code you posted is about. Without giving it too much thought I might be tempted to first find the fogged territories (relatively easy), and to pass those to the fog-of-war extended drawMap object, and have that extended drawMap ignore draw requests for territories outside the range of view.
The alternative of modifying each query which selects the data to draw individually, is probably going to be pretty tricky; selecting the out-of-view areas first and then creating a filter which gets rid of those as they come I think would be easier, and would apply to map rendering and to the JSON files equally, allowing some code re-use.

(This approach also allows for possible future optimizations based on generating a complete map and then generating each players maps and JSON files simultaneously by applying the fog-of-war for each player to the un-fogged map & JSON, which could offset much of the cost of needing individualized maps per player)


If you've already thought all that through and are looking for specific help with a query let me know the context of what those queries are and what problems you're facing and I'll see if I can help


Top
 Profile  
 
PostPosted: Sun Jun 20, 2010 8:30 am 
Offline

Joined: Wed Jul 29, 2009 10:22 am
Posts: 841
kestasjk wrote:
In a rather study averse mood, so I'll chime in; fog of war is a damn tricky one with many things to overcome:

All done.

The FogOFWar is already coded and works quite good on my site. (And it's working just in your current variant-system with one exception: I made the orderhistory class based, so I can block the output)

Quote:
If you've already thought all that through and are looking for specific help with a query let me know the context of what those queries are and what problems you're facing and I'll see if I can help


The result of the SQL-querry should be every "non-Fog" territory of the country $mcountry for a given turn.
The currently it's territories + units => +1 territory.
I want to make it SC-territories+units=> +1 territory, non-sc-territories=>just the territory.

Oliver


Top
 Profile  
 
PostPosted: Sun Jun 20, 2010 11:26 am 
Offline
Site Admin

Joined: Sat Jun 28, 2008 6:24 am
Posts: 892
That is impressive, can't wait to look through how you figured it all out, it is a juicy problem. I think I'll need more brainpower to try and help with your SQL than it takes to offer up some rambly thoughts though, which I'm not up to right now :(

It would be useful to get some comments if you could insert them, maybe on the context in which the SQL is being called just to give some background etc. You're doing some very funky inner joins on queries aliased as mab and tsb, which I'd love to spend some free time puzzling over and figuring out what's going on there, since I'm all about those neat expressive queries, but it'd probably be quicker with some comments and so on saying what you're doing if you think you'll need a hand with it3

But Ill try and take a closer look at these when I get a chance, because it looks like fun but I really must avoid the temptation right now


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group