I think Tom was asking if code in the browser could issue sql queries to a remote db, which I don't think sql.js can do. I think it is in the same category as sqlite.org's sqlite-wasm package. I went with the latter, as it seemed to have the best option to make the db persistent - this means there is a potential of doing a bulk load then incrementally updating it.
On Saturday, 1 February 2025 at 7:45:43 pm UTC+10 [email protected] wrote: > 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/eb6cbc9b-32ea-4fbd-b676-716d2722cf18n%40googlegroups.com.
