LonelyCache

Corfman Clan wrote: here's the SQL query for the above pic, another small glimpse into the LonelyCache database schema:

Code: Select all

SELECT Row,Ranking,CacherID,Cacher,Total
      ,(select sum(OverallPoints)from GeocacherPoints
        Where CacherID=r.CacherID and job=1 and RegionID = 1) FindsTotal
      ,(select sum(OverallPoints)from GeocacherPoints
        Where CacherID=r.CacherID and job=2 and RegionID = 1) HidesTotal
 FROM
 (SELECT
   ROW_NUMBER() OVER (ORDER BY Total DESC, g.Name) AS Row
  ,RANK() OVER (ORDER BY Total DESC) AS Ranking
  ,CacherID, g.Name as Cacher, Total
  FROM
 (SELECT CacherID, SUM(g.OverallPoints) AS Total
  FROM dbo.GeocacherPoints g
  WHERE regionid = 1 AND Job IN (1,2)
  GROUP BY CacherID) s
JOIN dbo.Geocacher g ON g.ID = CacherID) r
WHERE Row between 1 and 100
ORDER BY Row

rocketsciguy wrote:So, because I noticed, I have to ask: Why the one letter source tables/queries names? "r", "g", "s", "p"? Or is this some syntax with which I am unfamiliar?

rocketsciguy wrote:Are the webpages static HTML generated en masse with each update (hope not), or generated on demand from a set of template pages which use, e.g., PHP fields passed through the URL to query the database?

rocketsciguy wrote: Looks like you have the point totals pre-computed and stored in tables, probably with the heavy crunching performed offline then pushed to the server, is that right?

rocketsciguy wrote: It would be cool if the Live API data were fed directly into your live database, and all the supporting queries update with new data as it comes in. But I imagine that would be difficult to keep from breaking and might be "unfair" because of the round-robin way the Pocket Queries bring in data, and would likely bog down the server enormously. Just displaying this Top 10 rankings would require that every cache's points be recomputed, LCP-wide, and every cacher's total hide and find scores be summed -- looks like you're taking the sane route of doing those computations off-line.

Corfman Clan wrote:One problem with this is that while the statistics are being updated, anyone browsing the leaderboards will probably see a lot of garbage and have poor response. I'm thinking we may put up some banner or disable disable the leaderboard pages while this is going on.

Corfman Clan wrote:If the predicate is another select, then it has to be named. So, SELECT a FROM (SELECT c FROM d) b the "b" is giving a name to the select within the parenthesis. This is the case with the "r" and "s". I picked "r" for selecting the rank, and "s" for selecting the sum.

Corfman Clan wrote:The website is using Microsoft's .NET so the pages are essentially templates with server side code that runs when they are requested....
Well, when the leaderboard page is requested, there will be some parameters passed in the URL that specify the region and board and the page of the board to display. The server side code will evaluate all that and modify the query as needed then execute the query. When the results come back, the server side code will then generate the HTML for the page and that is then sent by the web server to your webbrowser and displayed.

Corfman Clan wrote:Yes, things are set up to update the statistics once a day once things are up to date. We decided to not spend the time updating the statistics if data gathering is taking place. On the test site, that is set to 4:00 am Eastern time. Usually they're done a few minutes after 4, other times they don't run until a few hours later. Updating the statistics seems to take a few minutes of pretty intense number crunching on the database server. That time will only grow as the number of caches, cachers, and logs increase and as we add more statistics. The updates are done online at the server though. That is, all statistic updates are actually SQL update or insert queries....
Like I mentioned above, the statistics updates are being done online, not offline. They are only being run once a day though and only then if no data gathering is taking place. We may update a cache's points as we gather data, but we will not be updating the statistics used to generate the leaderboards. Our feeling is that updating the statistics in realtime would be less meaningful than once a day and we took that into account when we came up with the current design. One problem with this is that while the statistics are being updated, anyone browsing the leaderboards will probably see a lot of garbage and have poor response. I'm thinking we may put up some banner or disable disable the leaderboard pages while this is going on.

rocketsciguy wrote:So if I read that right, you effectively have two layers to the database (perhaps it's really two separate databases, but whatever). The first layer is an accumulator -- it receives the data from the PQs and the API. The second layer is the daily snapshot. So at 4 AM ET, the server generates some UPDATE and INSERT queries for the database that makes aggregate statistic tables. Those tables become the new daily snapshot layer. At the user level, if a page requires aggregate data, it's pulled from the daily update layer. If it needs non-aggregate data, it could be pulled from the accumulator tables or from a static version of them generated at the same time as the daily snapshot. The advantage of using the accumulator data directly is could be more up-to-date, with the disadvantage that occasionally there will be a mismatch between (for instance) a cache's own reported ₧ value and the value that appears on a top 100 list of caches. The upside and downside for using a static version would be vice versa. This is great! I'm glad you guys have thought this through. Thanks!

• Geocache: Contains a row for each geocache. Each row contains all data for a geocache, such as GC code, name, location, owner, current point calculations, number of finds, etc. The DB on my laptop currently has 69,274 geocaches.
• Geocacher: Contains a row for every geocacher that has found or hid a cache in the LonelyCache domain. Each row contains the geocacher ID and name. My laptop DB currently has 114,076 rows.
• Logs: Contains a row for every geocache find. Each row contains the log ID, logger (geocacher), date, and geocache it is for. My laptop DB currently has 4,361,046 rows.
• Region: Contains a row for each region. Each row contains a region ID, name, and region type. Currently we have 6 regions, one for LonelyCache wide, and one for AZ, CO, NV, NM, and UT.
• GeocacheRegions: Contains a row for each region each geocache is in. Each row contains the cache ID and the region ID. Currently every cache is in two regions, LonelyCache Wide and whatever state it is in. If we add more regions, such as counties, then a cache may end up being in more than two regions.
• GeocacherPoints: Each row contains the geocacher ID, region ID, job, and point totals for each cache type, along with a point total for all cache types.

• Update the points and number of finders for each geocache.
• Delete every row in the GeocacheRegions table and then repopulate the table. This will most likely change when we add regions based on polygons in addition to the current LonelyCache wide and state regions.
• Delete every row in the GeocacherPoints table and then repopulate the table. Population of the table is an iterative process for each job and geocache type.

rocketsciguy wrote:Care to share more details about how this is deployed and hosted? You mentioned it running on MS SQL Server and hosted on the east coast. Was the decision to go with MS SQL Server mostly due to compatibility with .NET and C# with which you are already proficient? Will there be ongoing licensing expenses associated with running on an Microsoft base that could be avoided if the project were migrated to free/open-source software (i.e. running on a LAMP server = Linux, Apache, MySQL, Perl/PHP/Python)?

Corfman Clan wrote:The basic tables are:

• (Snipped descriptions for: Geocache, Geocacher, Logs, Region, and GeocacheRegions)
• GeocacherPoints: Each row contains the geocacher ID, region ID, job, and point totals for each cache type, along with a point total for all cache types.

SELECT Row,Ranking,CacherID,g.Name AS Cacher,Points AS Total
    ,(SELECT SUM(fp.Points) FROM GeocacherPoints f
      WHERE f.CacherID=p.CacherID AND f.RegionID=p.RegionID AND f.Board=(p.Board-20)
     ) AS FindsTotal
    ,(SELECT SUM(hp.Points) FROM GeocacherPoints h
      WHERE h.CacherID=p.CacherID AND h.RegionID=p.RegionID AND h.Board=(p.Board-10)
     ) AS HidesTotal
FROM GeocacherPoints p
JOIN Geocacher g ON (g.ID = p.CacherID)
WHERE RegionID=1 AND Board=21 AND Row BETWEEN 1 AND 100
ORDER BY Row

Corfman Clan wrote:

Corfman Clan wrote:The basic tables are:

• (Snipped descriptions for: Geocache, Geocacher, Logs, Region, and GeocacheRegions)
• GeocacherPoints: Each row contains the geocacher ID, region ID, job, and point totals for each cache type, along with a point total for all cache types.

We updated the GeocacherPoints table to only include one Points value instead of one for each cache type. We also added a Rank and Row field and renamed the Job field to Board.

Values for Board are: FinderOverall, FinderTraditional, FinderMulti, ... FinderWherigo, HiderOverall, ..., Hider Wherigo, ..., CombinedOverall, ..., CombinedWherigo, ..., BCOverall, ..., BCWherigo, FDF.
..
This added a lot more rows to the table and increased the time it takes to compute leader boards, but it really simplified things overall. It also speeded things up a lot when browsing the website. Displaying the Region and Geocacher pages, along with the leader board pages is much snappier. This is mostly because the ranking is now computed a priori instead of on demand.

Corfman Clan wrote:Updating the stats works well, but there is a flaw in it, in that when the stats are being updated, there is essentially incomplete data to display back to users. It takes minutes to update the stats so there are minutes where users will get garbage when a page is displayed. Redfist and I need to take a look at some options on dealing with this, or at least minimizing the amount of time that this situation exists.

Corfman Clan wrote:In general, both solutions are the same in that we have a set of statistics that is marked active. We keep that set around while the new set of statistics is being generated. Once the new set of statistics is finished being generated, then it is marked active and the older set is marked out of date and later deleted. Changing the new set to active and the old to out of date is essentially instantaneous, so there is no down time as far as anyone can tell when viewing the website.

rocketsciguy wrote:Happy to hear that you've found a way to fix this problem. A fortunate consequence of this solution is that you have the potential to generate historical (that is, time-varying) statistics. Old point values for individual caches probably aren't that interesting, but tracking cachers' score totals over time or leaderboard movements could be interesting. Some people have asked for an equivalent to DGP's front page stats of CP Gains and CP Finds -- who was the top lonely cache point finder in the last 30 days or 6 months? Who lost the most points (Achielle's, archived)?

• GeocacherID
• Date
• Points
• Points from Hides/finds in the last 30 days
• Points from Hides/finds in the last 180 days