SQLite schema report

[David Prellwitz]

Looking through both SQLite and Weewx documentation, i can't find a reference to a tool or report that will take a existing SQLdb and display the underlying DB's structure? I can see that wee-debug will give a listing of fields and key's, but there's no references to the relationships established for the exiting db. I ask because i'd like to understand how the existing data maintains keys. i.e., if data is stored with the only primary key as epoch time, can weewx manage joined keys of epoch+Device-ID? That would allow me to store multiple occurrences of epochDT, but different devices. that would allow me to store data from devices as raw data and data from WF (or other weather services) corrected data concurrently. It would also solve the often-mentioned "What's raw data and what has WF 'conditioned' data in my SQLdb?" issues.

/David

[vince]

echo ".schema" | sqlite3 mydatabasename.sdb

or look at https://github.com/weewx/weewx/blob/master/bin/schemas/wview_extended.py for the default schema for current v4 weewx.

[David Prellwitz]

Vince - I did all of that and the schema view is bare, i.e., it's still a whole lot of nothing  other than the field names - and it's not complete (or even close). I've run a Visio Data Model on Weewx.sdb (my running copy) and found 114 separate tables; many matching field names. All with the same PK and little - if any - SK/FK to differentiate what device/station generated that instance of data. And, all without (relationship) links. All without documentation or a word anywhere as to what some or most of the tables are for and/or pertaining to. 

So, rather than slogging through a morass of blogs, texts and various thousands of pages to try to figure out what most of those tables are for, i'm okay with adding notes and documentation to my Visio workspace and publish that info as i complete each piece. I just need people to send me, or provide links to, any specific documents describing these tables. Better, if something exists that shows some/most of this info, please provide it. 

[David Prellwitz]

Also, i'll try to just document the base and extended schema's to give a good base model. 

p.s., I'm using Visio as it's the tools i've used for years. If you have a suggestion for another tool, let me know and i'll see if i can load/install/use that tool. 

[vince]

I'd suggest your commentary is more than a little bit unfair and inaccurate, and I'll leave it at that.

Weewx by default uses an underlying sqlite3 database and puts its readings into an 'archive' table that has a large number of fields for the actual weather measurements.

CREATE TABLE archive (`dateTime` INTEGER NOT NULL UNIQUE PRIMARY KEY, `usUnits` INTEGER NOT NULL, `interval` INTEGER NOT NULL, `altimeter` REAL, `appTemp` REAL, `appTemp1` REAL, `barometer` REAL, `batteryStatus1` REAL, `batteryStatus2` REAL, `batteryStatus3` REAL, `batteryStatus4` REAL, `batteryStatus5` REAL, `batteryStatus6` REAL, `batteryStatus7` REAL, `batteryStatus8` REAL, `cloudbase` REAL, `co` REAL, `co2` REAL, `consBatteryVoltage` REAL, `dewpoint` REAL, `dewpoint1` REAL, `ET` REAL, `extraHumid1` REAL, `extraHumid2` REAL, `extraHumid3` REAL, `extraHumid4` REAL, `extraHumid5` REAL, `extraHumid6` REAL, `extraHumid7` REAL, `extraHumid8` REAL, `extraTemp1` REAL, `extraTemp2` REAL, `extraTemp3` REAL, `extraTemp4` REAL, `extraTemp5` REAL, `extraTemp6` REAL, `extraTemp7` REAL, `extraTemp8` REAL, `forecast` REAL, `hail` REAL, `hailBatteryStatus` REAL, `hailRate` REAL, `heatindex` REAL, `heatindex1` REAL, `heatingTemp` REAL, `heatingVoltage` REAL, `humidex` REAL, `humidex1` REAL, `inDewpoint` REAL, `inHumidity` REAL, `inTemp` REAL, `inTempBatteryStatus` REAL, `leafTemp1` REAL, `leafTemp2` REAL, `leafWet1` REAL, `leafWet2` REAL, `lightning_distance` REAL, `lightning_disturber_count` REAL, `lightning_energy` REAL, `lightning_noise_count` REAL, `lightning_strike_count` REAL, `luminosity` REAL, `maxSolarRad` REAL, `nh3` REAL, `no2` REAL, `noise` REAL, `o3` REAL, `outHumidity` REAL, `outTemp` REAL, `outTempBatteryStatus` REAL, `pb` REAL, `pm10_0` REAL, `pm1_0` REAL, `pm2_5` REAL, `pressure` REAL, `radiation` REAL, `rain` REAL, `rainBatteryStatus` REAL, `rainRate` REAL, `referenceVoltage` REAL, `rxCheckPercent` REAL, `signal1` REAL, `signal2` REAL, `signal3` REAL, `signal4` REAL, `signal5` REAL, `signal6` REAL, `signal7` REAL, `signal8` REAL, `snow` REAL, `snowBatteryStatus` REAL, `snowDepth` REAL, `snowMoisture` REAL, `snowRate` REAL, `so2` REAL, `soilMoist1` REAL, `soilMoist2` REAL, `soilMoist3` REAL, `soilMoist4` REAL, `soilTemp1` REAL, `soilTemp2` REAL, `soilTemp3` REAL, `soilTemp4` REAL, `supplyVoltage` REAL, `txBatteryStatus` REAL, `UV` REAL, `uvBatteryStatus` REAL, `windBatteryStatus` REAL, `windchill` REAL, `windDir` REAL, `windGust` REAL, `windGustDir` REAL, `windrun` REAL, `windSpeed` REAL);

If you look at the beginning of the line, you'll see the unique key is the dateTime (seconds since the unix epoch).

There are also a number of additional sqlite3 tables created, one per item above, with the dateTime for the record, the max/min for that measurement, when the max/min occurred, and also for things that are accumulated a running total.

CREATE TABLE archive_day_inTemp (dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY, min REAL, mintime INTEGER, max REAL, maxtime INTEGER, sum REAL, count INTEGER, wsum REAL, sumtime INTEGER);

These tables are not connected in any way at the database level, they're individual tables.  They're also using the dateTime of the record as the primary key for the records in the table.

Weewx handles the heavy lifting for keeping the summary tables up to date from the data in the archive table.   When you see people who ask how to clear bad data from their (archive table in the) database, they are generally told to 'drop daily' (which deletes the summary tables) and 'rebuild daily' (which regenerates the summary tables from the hand-modified archive table).

That help any ? 

I guess I'm kinda speechless re: using a drawing tool to visualize a database nor really what you're trying to do.   Perhaps the tool you want to use is google and going through the sqlite3 documentation online to better understand how to reverse engineer and examine a sqlite3 database, but I guess that's a bit harsh.  I'll assume that you're overthinking things a bit and looking for complexity where it doesn't exist.

• archive table has all the 'meat'
  
• summary archive_whatever tables have the pre-computed summaries of max/min/sums of each element for later use
  

[David Prellwitz]

Thanks for the quick reply!  I apologize for any negative connotations (not intended), i guess my perceptions and assumptions are off-base! it's just in all my professional DB roles (25yrs +) i've had to use visual layout tools to understand existing data creation, utilization, ranges, applications and operational impacts. These visual tools allowed us to use as much third-normal form reduction steps as possible to simplify data architecture to the lowest possible level. My misjudgement of WeeWx layout and structure is just that, my misjudgement.

Perhaps i am over-thinking things as i try to get basic things done. Habits of a long life in IT Management/DB Architecture/Programming. I just think i may be some ideas and observations that may improve WeeWx but, I can drop these distractions if it's an issue.

Now, the issue I had was the lack of understanding the role of the additional tables - they didn't seem to have any relationship to the archive table; other than Epoch. If i'm reading your statement correctly, all the other tables are the detail records for each of the elements in the archive table - just related by element type ("Name"?) and epoch timestamp? So if i went looking for "Wind Gust Direction" instances, I'd look in the Archive table for "WindGustDir" and find an entry (which represents what, a summary, an average, ?),  and one or more "Archive_day_windGustDir" entries, depending on the frequency of that element's recording? (Assuming archived wind-gust data is recorded for one-minute intervals, but "Archive_day_windGustDir" are recorded at 3second intervals for Wind-Gust-Dir table.)  Knowledge at that level of detail should be known somewhere, right? Perhaps somewhere in GitHub?

How is this useful? It appears to me (IMHO) that a good understanding of the data flow and mapping could help reduce event process time; reduce data storage needs; increase functionality; improve simplicity and make it a better product. Looking at the architecture of WeeWx, the process diagram shows a single thread for each event (i.e., UDP packet arrival); followed by event processing; followed by data-recording; followed by report generation. All of these appear to be serial. If the front end was to be multi-threaded to allow multiple device/station packet reception and placed in a triggered repository that the rest of WeeWx processed, wouldn't WeeWx be able to process multiple stations data without having multiple instances of WeeWx (and multiple SQL db's). A quick, multi-threaded front-end could be prioritized to allow for sub-second processing. Leaving more "leisured" approach for the rest of Weewx to process and create reports.

I'm a bit rusty in data-mapping (did it from 1975 through 1998) so i'm sure any post-grad would be sharper at this then I am.  Perhaps having some Data Science Professor create a post-doc project that would help (assuming i'm not too far off base here)!  Being retired, i now have time on my hands to assist if anyone thinks that would be useful.

[David Prellwitz]

Side note:

re-reading Vince's post, i had the process backwards - detailed event records in Archive table; summary/special info in separate tables...

[vince]

If you want to talk about reengineering things under the hood, that might be better discussed in the weewx-development group, and I'm sure Tom and the usual others will chime in a lot there item-by-item.

But yes, the operative word there is 'summary' in the table names.   Weewx keeps the summary tables up to date for (I'm guessing) performance reasons.   There might be a little ancient history as well based on the original wview-compatibility goals regarding the naming conventions and the like.

My over-simplified understanding of the various summary tables is they are running totals of the measurements for the day and how many measurements they are, with the min/max and time of min/max recorded, as well as a weighted sum.   Things that are directional in nature are a bit more complicated I guess, but I never worried it basically so I don't know there.

One resource you might look at is http://weewx.com/docs/customizing.htm which has some architectural things in it.

[Tom Keffer]

Yes, the other tables are "daily summaries." They are strictly an optimization. There's nothing in them that cannot be extracted from the main archive table and, indeed, they can be rebuilt from the latter using the tool wee_database.

WeeWX is multi-threaded, but not in the way you describe. Yes, there is a single "main" thread that blocks, waiting for data, then process it, then stores it in the database. A separate thread handles reporting. Still other threads upload through a RESTful facility.

On the surface, multi-threading data acquisition (or, alternatively, using an async interface) sounds attractive. However, if the goal is to acquire data simultaneously from several sources, that can as easily be accomplished by launching multiple instances of WeeWX. If the goal is to intertwine the various sources and have them all stored in the same table in the same database, that is far more complicated. The simple architecture of WeeWX would be lost.

However, if you have some concrete, specific, ideas on how to service more than one device simultaneously, then I'm all ears. I would really love to take advantage of the new "async" interfaces in modern versions of Python (unfortunately, the 3rd party libraries needed to use them have not caught up. Specifically, async versions of pyusb and pyserial). But, we are way beyond general ideas. What's needed is a working framework, even if it's merely a prototype.

Through the years, these ideas have come up from time to time. Various alternative architectures have been considered. However, simplicity has always won out. I think the rich ecosystem of 3rd party extensions that surrounds WeeWX has shown that to be a good choice. 

-tk

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/59c1ba34-84c8-42f7-8b01-249f9b25b350n%40googlegroups.com.

[David Prellwitz]

Thank you both for the replies! I've dived into the end of this pool without comprehending its depth.

This all started after spending more than a few frustrating days trying to find the documents for, or figure out a proper set of, field maps to cover three apparent subsets of WeatherFlow data into the Weewx (UDP capture from station; Lightning data import; API get from WF) plus slightly different field names from some of those CSV headers.  As i looked over each of the CSV header entries, each header seemed to have several anomalies (names, abbreviations). Then i couldn't find the unit values for each as compared to what Weewx was looking for.  I started looking for a definitive source for field data-type/unit default values. Then i started looking for what each of the field elements actually represented, eg - link wind elements (wind gusts, wind lulls, etc), lightning elements (energy, avg distance), precipitation elements, and other elements.   I started to map all the published fields from WF, Weewx, GitHub, Google Groups, etc to see if i could document each use to a valid Weewx field name. So, i found several lightning elements mapped to fields with "XXX", "YYY", signal7, signal8, so on and so forth. I started looking for a Tempest station data mapping info similar to other station data (Acurite, CC3000, etc) found in the Hardware section, and tried to find measurement unit values (i.e., m/s vice meters-per-second; etc) and defaults for each field element. Not found. So i thought i'd try to map all of WF's elements, with measurement values, with weewx fields - as many as i could find. But, to do that, i need to know what each element represents - on both sides.

So that's were i sit - trying to use the tools i know to document WF/Weewx data fields. Can you provide some useful links and/or point me in the right direction to gather such info? I'll try to gather what i can and get it back to you if it doesn't exist,

thanks!

[vince]

That's a lot of words, but I still think you're possibly overthinking.

Anyway,  I'm pretty lost re: what you're actually trying to do.

• There is a sample mapping of Tempest UDP elements to Weewx database elements both in the driver code and in the docs on the github site for the driver.
• There are a couple forks of the original driver, but they all have an example looking like (this map)

So using outside temperature as an example, you'll see a mapping that looks like:

• outTemp = air_temperature.ST-00000025.obs_st

Which means:

• map the driver's air_temperature element
• for sensor ST-00000025
• from the obs_st observation type emitted by the WF gear
• to weewx's outTemp database element

The driver's terminology is in the code around line 211 of the driver (here).   So why is it different ?  Because the driver references the WF UDP API (here) which uses the description 'air temperature' in the obs_st (tempest observation) type.   The driver is written to reconcile versus the vendor's public API.   The mapping reconciles that to weewx-speak.

So what units does outTemp use and how do you figure THAT out ?

• it's set in your weewx.conf or skin.conf for the item group_temperature
• which is documented in the Customization Guide appendix detailing the units weewx uses for each thing in the database

So if you want to see the mapping of any database element to units, just reference the Customization Guide appendix.

This stuff really is documented in excruciating detail, but it is a bit of a trail of things to follow to put all the pieces together notionally.

[David Prellwitz]

Vince - yea, my way of apologizing for running down some rabbit hole i didn't need to. Thanks for the guidance, I'll map it out and see if it works. I'll try to get a decent mapping setup and forward to you for comment.

My next attempt is to install and use Belchertown skins.  

[vince]

It's not rocket science.  Don't overthink it.   It's solid under the hood.

I still don't know what mapping you're looking for that hasn't been asked+answered already, but dozens of people use WeatherFlow and hundreds use Belchertown, so whatever you're looking to do has almost certainly been asked and answered already here if you use the google group search of the archives here.

[David Prellwitz]

Vince, if i'm reading your post correctly (and carefully reading the links you've provided), weewx.sdb stores all elements in a unit-base type as specified in the usUnits group  ("Each observation type, such as outTemp or pressure, is associated with a unit group, such as group_temperature or group_pressure. Each unit group is associated with a unit type such as degree_F or mbar."  So, as my weewx.conf has an entry in [StdConvert] section "target_unit = us " i can assume that all weewx.sdb entries conform to the US Group column. further assumption is that if WeatherFlow Tempest device provides data in m/b and i spec it as m/b for the LOOP process, it will be converted to inHg as part of the UDP LOOP process. If i import data from WF using the API's i just need to spec the API page's listing of unit WF will be providing for those fields and wee_import will correct those to the weewx.sdb needed units?  If i wish to have lightning information, i'll need to map to those "signal7/8" and/or "xxx/yyy" fields, and i'll have to figure out what units they use?

thanks!

[David Prellwitz]

You've been patient, thanks! I'll review the links and extensions and move forward. 

[vince]

Lightning is difficult because there's no units for 'energy' in the UDP API.  I might also add perhaps don't bother, as lightning is bogus anyway.  It's what your sensor reported, but it is not remotely close to the crowd-sourced info their web or mobile interfaces provide, of which your sensor measurements are just a small part of what they aggregate and display.

I think the simplest thing to do is map stuff and see what the driver puts in the db.   It's possible the driver forces metric (or not metric) and does the conversions under the hood before weewx ever sees the assembled LOOP.  Sometimes it's easier to just look at the code or do a little experimentation.

[David Prellwitz]

error trying to install belchertown. my weewx install was using setup.py. I downloaded the belchertown 1.2 tar.gz into my home/david/Downloads folder. I ran the sudo wee-extension command and get "File "/home/weewx/bin/weecfg/extension.py", line 134, in install_extension weecfg.extension.InstallError: Extension '/home/david/Downloads/weewx-belchertown.1.2.tar.gz' not found.

can't find what i did wrong. Should i extract the tar.gz file first? move it to another folder to start install?

/David

[Glenn McKechnie]

/home/david/Downloads/weewx-belchertown.1.2.tar.gz

Check the filename

Is it really ... weewx-belchertown.1.2.tar.gz ( as per your snippet)
or is it ... weewx-belchertown-1.2.tar.gz as per the release page at
https://github.com/poblabs/weewx-belchertown/releases/tag/weewx-belchertown-1.2

note: there are 2 dashes in the correct name.

>>> as specified in the usUnits group (*"Each observation type, such

>>> as outTemp or pressure, is associated with a unit group, such
>>> as group_temperature or group_pressure. Each unit group is associated
>>> with

>>> a unit type such as degree_F or mbar.*" So, as my weewx.conf has an

>>> entry in [StdConvert] section "target_unit = us " i can assume that all
>>> weewx.sdb entries conform to the US Group column. further assumption is
>>> that if WeatherFlow Tempest device provides data in m/b and i spec it as
>>>
>>> m/b for the LOOP process, it will be converted to inHg as part of the UDP
>>>
>>> LOOP process. If i import data from WF using the API's i just need to
>>> spec
>>> the API page's listing of unit WF will be providing for those fields and
>>>
>>> wee_import will correct those to the weewx.sdb needed units? If i wish
>>> to
>>> have lightning information, i'll need to map to those "signal7/8" and/or
>>>
>>> "xxx/yyy" fields, and i'll have to figure out what units they use?
>>> thanks!
>>>
>>>
>>> On Wednesday, March 3, 2021 at 11:34:05 AM UTC-8 David Prellwitz wrote:
>>>
>>>> Vince - yea, my way of apologizing for running down some rabbit hole i
>>>> didn't need to. Thanks for the guidance, I'll map it out and see if it
>>>> works. I'll try to get a decent mapping setup and forward to you for
>>>> comment.
>>>> My next attempt is to install and use Belchertown skins.
>>>>
>>>> On Wednesday, March 3, 2021 at 8:31:12 AM UTC-8 vince wrote:
>>>>
>>>>> That's a lot of words, but I still think you're possibly overthinking.
>>>>>
>>>>> Anyway, I'm pretty lost re: what you're actually trying to do.
>>>>>

>>>>> - There is a sample mapping of Tempest UDP elements to Weewx

>>>>> database elements both in the driver code and in the docs on the
>>>>> github
>>>>> site for the driver.

>>>>> - There are a couple forks of the original driver, but they all

>>>>> have an example looking like (this map)
>>>>>

>>>>> <https://github.com/jjvdgeer/weatherflow-udp/blob/master/sample_Tempest_sensor_map>

>>>>>
>>>>> So using outside temperature as an example, you'll see a mapping that
>>>>> looks like:
>>>>>

>>>>> - outTemp = air_temperature.ST-00000025.obs_st
>>>>>
>>>>> Which means:
>>>>>
>>>>> - map the driver's air_temperature element
>>>>> - for sensor ST-00000025
>>>>> - from the obs_st observation type emitted by the WF gear
>>>>> - to weewx's outTemp database element

>>>>>
>>>>> The driver's terminology is in the code around line 211 of the driver
>>>>> (here)

>>>>> <https://github.com/jjvdgeer/weatherflow-udp/blob/master/bin/user/weatherflowudp.py>.

>>>>>
>>>>> So why is it different ? Because the driver references the WF UDP
>>>>> API

>>>>> (here) <https://weatherflow.github.io/Tempest/api/udp/v143/> which

>>>>> uses the description 'air temperature' in the obs_st (tempest
>>>>> observation)
>>>>> type. The driver is written to reconcile versus the vendor's public
>>>>> API.
>>>>> The mapping reconciles that to weewx-speak.
>>>>>
>>>>> So what units does outTemp use and how do you figure THAT out ?
>>>>>

>>>>> - it's set in your weewx.conf or skin.conf for the item
>>>>> group_temperature
>>>>> - which is documented in the Customization Guide
>>>>> <http://weewx.com/docs/customizing.htm#units> appendix detailing

>>>>> the units weewx uses for each thing in the database
>>>>>
>>>>> So if you want to see the mapping of any database element to units,
>>>>> just reference the Customization Guide appendix.
>>>>>
>>>>> This stuff really is documented in excruciating detail, but it is a bit
>>>>>
>>>>> of a trail of things to follow to put all the pieces together
>>>>> notionally.
>>>>>
>>>>>
>

> --
> You received this message because you are subscribed to the Google Groups
> "weewx-user" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to weewx-user+...@googlegroups.com.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/weewx-user/98dd7083-29da-4cb3-9679-b5c391df6c65n%40googlegroups.com.
>


--


Cheers
Glenn

rorpi - read only raspberry pi & various weewx addons
https://github.com/glennmckechnie

[David Prellwitz]

Vince - thanks! I'll take that approach on the imports!

Glenn:
I had hand-transcribed the command entry - i'd entered the correct command string:

sudo /home/weewx/bin/wee_extension --install /home/david/Downloads/weewx-belchertown-1.2.tar.gz |& tee /tmp/wee-extension-out.txt

This is the results:

======================

Request to install '/home/david/Downloads/weewx-belchertown-1.2.tar.gz'
Traceback (most recent call last):
  File "/home/weewx/bin/wee_extension", line 88, in <module>
    main()
  File "/home/weewx/bin/wee_extension", line 80, in main
    ext.install_extension(options.install)

  File "/home/weewx/bin/weecfg/extension.py", line 134, in install_extension

    raise InstallError("Extension '%s' not found." % extension_path)
weecfg.extension.InstallError: Extension '/home/david/Downloads/weewx-belchertown-1.2.tar.gz' not found.

========================

No info about where to put the tar.gz file. Again, i'd used the "setup.py" script to install weewx, so all the relevant folders are off "/home".

/David

[gjr80]

Did you ls the directory containing the extension package to confirm the file name? According to the Belchertown repo the 1.2 extension package is named weewx-belchertown-release-1.2.tar.gz, slightly different to that used in your commands.

Gary

[David Prellwitz]

Gary:

ls -l /home/david/Downloads:

-r-------- 1 david david 1518145 Mar  3 18:02 weewx-belchertown-release-1.2.tar.gz

and i used the fully qualified sudo string for execution:

sudo /home/weewx/bin/wee_extension --install /home/david/Downloads/weewx-belchertown-1.2.tar.gz

[gjr80]

Perhaps I need to change my glasses, but the last time I looked weewx-belchertown-release-1.2.tar.gz is different to weewx-belchertown-1.2.tar.gz.

Perhaps you should try:

$ sudo /home/weewx/bin/wee_extension --install /home/david/Downloads/weewx-belchertown-release-1.2.tar.gz

Gary

[David Prellwitz]

Duh, perhaps i ! those glasses! what's a 'release' between commands, anywho? thanks!