> I think the only sane way would be code on the server interrogating the DB and providing an api.
With another project I use this approach: the backend is running a spring boot application on my home server, gathering all the data from my PV, storage battery and smart meter. I wanted this application to be accessible from the internet without having to connect to my home server. So I implemented the REST API also in a php script that resides on a managed webspace, the backend server is syncing the the local sqlite db to the remote sqlite db just for the missing lines, every archive interval. The HTML/JS part interacts with the php-REST-API, just as it does with the Java REST-API so the only thing that has to be maintained in two time is the very core of the REST API that is providing data for the front end. This could be an approach for a skin, with the limitation, that for using the feature, there needs to be php with sqlite available (which my managed webspace includes out-of-the-box). But with this approach you need to do a lot of things (formatting values, unit conversions, ...) in the front end, instead of using weewx that already solved all this stuff for you. On the other hand this is already the case for fuzzy-archer: incoming MQTT data needs to be converted and formatted in the front end, using configuration from the back end transported to the front end, so all information is already there in my particular case, you just need to do it for a bit more of data. [email protected] schrieb am Samstag, 1. Februar 2025 um 10:45:43 UTC+1: > Yes, sql.js, it can use an Uint8Array representing an SQLite database > file. With only about 10 years of data mine is sized ~300MB, so loading > the db data from the server each visit can't be the solution. > > rsyncing would require to transfer the whole database, wouldn't it? That > would also cause huge amounts of traffic. > > Tom Keffer schrieb am Freitag, 31. Januar 2025 um 23:16:02 UTC+1: > >> Is there a version of sqlite that runs in the browser, but queries a >> sqlite file on the webserver? The sqlite file could be kept up-to-date by >> using rsyncs from the server that WeeWX is running on. >> >> On Thu, Jan 30, 2025 at 4:30 AM '[email protected]' via >> weewx-development <[email protected]> wrote: >> >>> Thanks for the inputs. >>> >>> For viewing a day some years ago, just like today, daily summaries lack >>> the resolution of the archive_interval. Daily summaries, or whatever >>> interval is chosen for the week/month/year might be necessary to provide as >>> well, if the aggregation shouldn't be done in the front end. >>> >>> > Your idea of using a JS library for server-side SQLite is >>> interesting, but wouldn't it have to run in the browser? >>> >>> I think that's the idea of https://sql.js.org/ For the whole database >>> this probably isn't a feasible approach, because the client needs to >>> download the whole database from the server into memory. Transferring more >>> or less raw data to the same serve the web page resides and doing partially >>> the same stuff there, what weewx does or could do in the back end, seems >>> not too reasonable anyway. >>> >>> I think I'll play around with the following idea: JSONize the required >>> obs_types in chunks that cover a week, or a month or 10 days or 100 days, >>> containing the values for each and every archive_interval and then see, how >>> to handle them in the front end and if this approach behaves in terms of >>> performance. >>> >>> >>> > I am still fighting a bit with caching >>> >>> In which way? I had issues with caching JSON Data from files on the >>> webspace, I ended up adding a ?ts={currentDateTimeMillis} on every requests >>> that fetches (near) real time data, reading JSON files from the server. >>> Setting "no-cache" didn't work that well. I plan to do the same for all css >>> and js files that might change on an update of the skin, but instead of >>> setting using the current time i'll think i'll go for the datetime these >>> resources have changed on the backend. Like so: assume the user installed >>> the current version of the skin at 1738195200, i'll let the the python >>> script that produces JSON for the front end check the last modified date of >>> the skin's css and js directories. I pass the value to the templates so >>> they append ?ts=1738195200 to each included static resource. If this >>> value changes, the browser will fetch the stylesheet, because there is none >>> with that URL cached. Or something like that, e.g. fingerprinting the >>> contents and using the hash value as the parameter >>> >>> >>> >>> [email protected] schrieb am Donnerstag, 30. Januar 2025 um 02:14:54 >>> UTC+1: >>> >>>> I took the approach of exporting the ‘daily summaries’ to json. >>>> Technically I only export out what I need to chart (I use ECharts). If I >>>> had it do over again, I would export it out in a more generic format…. >>>> >>>> Using the ‘generate_once’ option, I managed to squeeze a bit faster >>>> generation time. Net, after the first run, it is pretty snappy (I only >>>> have >>>> data back to mid 2016). >>>> >>>> Where it gets a bit ‘wonky’ is supporting multiple database bindings. >>>> Again, if I had it to do over, I think exporting the different databases >>>> in >>>> a generic format would simplify this… >>>> >>>> The other wonky thing was eliminating ajax calls. I got around this by >>>> using iframes. It is a pretty big hack, but appears to work. >>>> >>>> I am still fighting a bit with caching. I think it works pretty well in >>>> most browsers, except in Safari sometimes Safari seems to get ‘stuck’. >>>> Honestly not sure where the cache problem is, host, browser, or somewhere >>>> between… >>>> >>>> I just brought up the site and looks like there is a new bug that >>>> causes some of the charts to not display. I’m not surprised. The skin is >>>> very experimental and was developed to see what could be done, hoping that >>>> someone would take it and make a ‘production’ version…. >>>> >>>> You can see it in action here, https://bellrichm.org/weather/# >>>> >>>> I too, look forward to what you come up with. >>>> rich >>>> >>>> On Wednesday, 29 January 2025 at 17:02:21 UTC-5 Tom Keffer wrote: >>>> >>>>> This is something that I've wrestled with in the past, but never came >>>>> up with a good solution. Most solutions require some sort of application >>>>> server to manage a database that is running on the same box as the >>>>> webserver. That requires the user to do another install, and it's usually >>>>> a >>>>> quite complicated one with proxies, etc.. This is the approach I took >>>>> with >>>>> the now defunct weert <https://github.com/tkeffer/weert-js>. It all >>>>> proved too complicated. >>>>> >>>>> The goal is something that requires zero changes on the >>>>> webserver platform. That is, no application server, no MySQL install, not >>>>> even a SQLite install. Everything is done by a browser script, which can >>>>> be >>>>> uploaded from the WeeWX server. >>>>> >>>>> One way to do this is, as you note, to also upload all historical data >>>>> as JSON files. I wouldn't completely rule that out. If you restrict the >>>>> data to daily summaries, it's probably only a few 10s of megabytes. >>>>> >>>>> Your idea of using a JS library for server-side SQLite is interesting, >>>>> but wouldn't it have to run in the browser? I'm not seeing how it could >>>>> run >>>>> on the webserver. Perhaps there's some extension for nginx that allows >>>>> this, but then the user is doing server installs, which is what we're >>>>> trying to avoid. >>>>> >>>>> Which brings us to another idea: a client-side database: IndexedDB. As >>>>> you upload JSON files, it would remember them, using them in the display. >>>>> There's the possibility that it could get evicted, which would require >>>>> rebuilding the IndexedDB database, which would require JSON files on the >>>>> server, so you're back where you started. >>>>> >>>>> I'll be interested to see what you come up with! >>>>> >>>>> -tk >>>>> >>>>> >>>>> On Wed, Jan 29, 2025 at 10:23 AM '[email protected]' via >>>>> weewx-development <[email protected]> wrote: >>>>> >>>>>> fuzzy-archer <https://github.com/brewster76/fuzzy-archer> a.k.a. >>>>>> "the Bootstrap skin" supports live data and interactive charts and >>>>>> gauges, >>>>>> out-of-the-box, for a 27h-period, if not configured else. >>>>>> >>>>>> Week/Month/Year-data is provided with static images. The users >>>>>> request interactive charts for all these timespans, and I am planning to >>>>>> implement this feature within the next year, if I find a realistic >>>>>> approach >>>>>> without having new requirements for hosting the front end. And I plan to >>>>>> make it possible, to provide all data from the database to the front >>>>>> end, >>>>>> making it possible to view history data, just as if it was today's data. >>>>>> >>>>>> I want to do it the most WeeWX-ish way possible and currently just >>>>>> thinking about the ways to get there. >>>>>> >>>>>> The first problem is: how to make all this data available. Currently, >>>>>> the data for the rolling, 27h view, is provided in a JSON file that is >>>>>> updated and uploaded to the front end every archive interval. Updating >>>>>> and >>>>>> uploading a JSON file holding all desired data for all time, since the >>>>>> station started, doesn't seem to be a sane approach. >>>>>> >>>>>> There is a JS library for SQLite, so an approach could be to synch >>>>>> all necessary data to a SQLite database on the web server, but how to >>>>>> get >>>>>> the data there? Per request, every archive interval? This would probably >>>>>> require some serve-side-scripting, which will limit this feature to >>>>>> servers, that provide support for that. >>>>>> >>>>>> Another approach: create (maybe compressed) chunks of historic data, >>>>>> that may be uploaded once and deflated using client side JS on demand. >>>>>> Challenge with this approach: how to set this up initially, creating and >>>>>> uploading all these files will probably take a while for stations with a >>>>>> longer history. In theory, since historic data shouldn't be subject to >>>>>> changes, this need only to be done once, and for new data, but new data >>>>>> will cover only a certain timespan, not decades of historic data. >>>>>> >>>>>> Any ideas for other approaches? Or is this just not realistic? >>>>>> >>>>>> >>>>>> -- >>>>>> You received this message because you are subscribed to the Google >>>>>> Groups "weewx-development" group. >>>>>> To unsubscribe from this group and stop receiving emails from it, >>>>>> send an email to [email protected]. >>>>>> To view this discussion visit >>>>>> https://groups.google.com/d/msgid/weewx-development/3940ac3c-9cfc-4069-a7a9-b63ee5761ec0n%40googlegroups.com >>>>>> >>>>>> <https://groups.google.com/d/msgid/weewx-development/3940ac3c-9cfc-4069-a7a9-b63ee5761ec0n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>>> . >>>>>> >>>>> -- >>> You received this message because you are subscribed to the Google >>> Groups "weewx-development" group. >>> To unsubscribe from this group and stop receiving emails from it, send >>> an email to [email protected]. >>> >> To view this discussion visit >>> https://groups.google.com/d/msgid/weewx-development/416788b0-a58c-4138-ad45-24e9af8977e9n%40googlegroups.com >>> >>> <https://groups.google.com/d/msgid/weewx-development/416788b0-a58c-4138-ad45-24e9af8977e9n%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >> -- You received this message because you are subscribed to the Google Groups "weewx-development" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion visit https://groups.google.com/d/msgid/weewx-development/cfdb0f16-9107-453e-adab-c65a3ebac0den%40googlegroups.com.
