dbname= functionality and archive databases

[Jim]

Quick summary: A little-known, rarely useful, and buggy feature exists where you add a dbname= query string parameter manually to TM URLs to use an alternate database.  As of this evening (on tmstage.teresco.org only for now) dbname= is much more reliable (though intentionally still not persistent), and I have created and populated a couple historical database instances called TM202506 and TM202507 that have the TM database as of the June 1 and July 1 site updates from this year.

Details:
Motivated by my interest in trying to see what my before and after travels looked like for the Bangor reroutes that yakra implemented, I decided to create and populate an instance of the TM database from the last monthly snapshot on July 1, 2025, called TM202507.  Then I could use the dbname= QS parameter to select it and the site would show everything as it was at that time in the UserData and HighwayData repositories.  Great, but dbname= never really kept up with the changes in the way TM accessed the DB since the current implementations of mapview and showroute came to be.  So Copilot and I set out to squash those bugs and (I think) we did.  I then created and populated a second historical database for June 1, 2025 (TM202506).

So now, for example, you can browse my travels and the TM highways as of June 1, 2025, in Mapview by adding dbname=TM202506 to its URL on tmstage:

https://tmstage.teresco.org/user/mapview.php?units=miles&u=terescoj&v=&dbname=TM202506

Sure enough, my travels around Bangor remain accurately mapped by my .list file's entries after the reroutes!

Another nice use case is to see how my stats have changed since June 1 by loading up my user page with the regular DB to see my latest totals, then specifying the DB to see what they were on June 1:

https://tmstage.teresco.org/user/?units=miles&u=terescoj&dbname=TM202506

I am pretty confident this is working well and more confident that it didn't break anything else, but I'd still like to get some others to try it out before I put this code on the main site.

Unlike u= and units=, the dbname= QS parameter is not persistent as you navigate the site.  This is intentional since I don't want someone to switch to an old DB and not realize they're still using it later on.

At the expense of about 1.5 GB of persistent storage on the server per instance, I can add more historical DB instances.  They should work as far back as whenever we last made any changes to the DB structure that would be inconsistent with the structure expected by the web front end.  I'd have to think about when that was.  I'm happy to add some more.

So please try this out and let me know if you notice any problems.  Also I welcome thoughts on how many and which archive databases should be made available.

[michih]

Just my 2ct on this...

I like the idea very much I think it could help to improve the update entries. https://travelmapping.net/devel/updates.php features the 'File Root' column. A new (automatically added by frontend) column could open showroute with dbname=TMxxxxxx where xxxxxx is the last stored alternate database before the date indicated in the 'Date' column. That means, with highway data and user data from the day (or week, or month or year) before the update was made.
If a routing was heavily changed, I usually check my travels on mapview. It would be great if I could directly switch to mapview with the current view.

I also love the idea of seeing stats and mapview at a previous point like before my last travels or at the end of last year

To be honest, dbname is not yet useful for me since it's not persistent. We could simply add a yellow banner - like the red 'update in progress' banner - that indicates 'You currently view historical data from xxxxxx'.

Having this in mind, I think that we should minimum have one historical database per year. Likely from Dec 31. A simple drop down menu could make the trick to select it on the front end. Could we also do this for 2015 to 2024 data? It's all on Github, but I assume it's hard to automate, isn't it?

If data space is not a big issue, I'd prefer having one historical database per month to better meet the use-case demands from above. Then, we would likely need a more sophisticated selection option.

I don't know what could be broken and how to test things on tmstage now. I cannot simply click around since the QS is not persistent. But all I quickly tried worked fine. Thanks for your work

[Jim]

Thanks, @michih. I especially like the idea of a banner when an alternate DB is selected.

https://github.com/TravelMapping/Web/issues/843

[mapcat]

Thanks for working on this excellent new feature. Like michih, I'd love it if we could have Dec 31 or Jan 1 database instances for each year. Providing one for each month in the history of the site is probably too much, but keeping one for each month for the current year would be useful.

[Jim]

The current functionality is now live on the main site.  I'll definitely be bringing in some more archive database instances.