There are two key Python classes involved in updating the databases:
Manager and DaySummaryManager. The latter is a subclass of the former.
If you add a record to the database using class Manager and method
addRecord(), it will get added *only* to the "archive" table. The daily
summaries are not touched.
However, if you add a record using subclass DaySummaryManager and method
addRecord(), it will first call its superclass's method,
Manager.addRecord(), which adds the record to the "archive" table, then it
will update the daily summaries as well.
There is no magic here: if you manipulate / delete something in the main
archive table, it would be up to you to update the daily summaries. Usually
it's easier to just drop all the daily summaries, then rebuild them.
To answer your last question: the daily summaries are just an optimization.
To do a query such as give me the min and max temperature for the year
using the archive table takes far longer than using the daily summaries.
Looking through some old notes, it took 0.06 second to find the max
temperature of the year from the archive table, and only 0.0004 using the
daily summaries. The very early versions of WeeWX did not do this
optimization and the query times were unacceptable.
I've attached the benchmark I used.
-tk
On Sat, Oct 13, 2018 at 10:19 AM CCOR58 <[email protected]>
wrote:
> Ok I see that the "archive" table is the master I would expect to see
> but am confused as to how/why all the other tables are created. Are
> they derived and updated from the archive table with individual update
> queries that run each time the archive table has a record added?
>
> If something in the archive table is manipulated manually will it be
> reflected in the archive sub tables or do they have to be modified /
> updated manually as well?
>
> If data from the archive table was way out of range for some
> maintenance procedure reason on the senors would deleting those records
> manually need to be done to each of the sub tables as well?
>
> Are the subtables created as child recordsets of the main archive table
> in order to allow some generated reports to access data more quickly ?
>
> Why do those reports just not run specific queries for that specific
> data selecting from the main table rather than creating extra separate
> tables?
>
>
>
>
>
> On Fri, 2018-10-12 at 16:55 -0700, Thomas Keffer wrote:
> > Did you check out the section The database in the Customizing Guide?
> > Take a look, then come back with any questions you might have.
> >
> > -tk
> >
> > On Fri, Oct 12, 2018 at 4:31 PM CCOR58 <[email protected]>
> > wrote:
> > > Is there any big picture tech descriptives about the tables in the
> > > weewx archive database?
> > >
> > > I connect to the mysql database using LibreOffice-base as well as
> > > phpmyadmin and I see a large number of tables but each table seems
> > > to
> > > only have two columns usually.
> > >
> > > I would like to create custom reports using libreOffice base but I
> > > need
> > > to get a better handle on what I am seeing.
> > >
> > > Usually I would expect to find a table with single record
> > > containing
> > > multiple fields each holding the latest data collected from the
> > > sensors
> > > at aspecified sample date and time.
> > >
> > > The weewx database appears to be set up under a different paradigm
> > > I
> > > fail to grasp.
> > >
> > > If there is any place that has refinfo that would help me
> > > understand
> > > what was used to design the database I would appreciate it.
> > >
> > > Thanks
> > > Andy
> > >
>
> --
> 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 [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
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 [email protected].
For more options, visit https://groups.google.com/d/optout.
import time
import configobj
import weewx.manager
start_tt = (2009, 1, 1, 0, 0, 0, 0, 0, -1)
stop_tt = (2010, 1, 1, 0, 0, 0, 0, 0, -1)
start_ts = time.mktime(start_tt)
stop_ts = time.mktime(stop_tt)
config_dict = configobj.ConfigObj('/home/weewx/weewx.conf')
database_dict = weewx.manager.get_database_dict_from_config(config_dict, 'archive_sqlite')
####################################
#
# Use the main archive table
#
# 0.29 for first run
# 0.06 for second run (presumably, after caches are filled)
#
manager = weewx.manager.Manager.open(database_dict)
sql_stmt = """SELECT dateTime, outTemp FROM archive WHERE
outTemp=(SELECT MAX(outTemp) FROM archive WHERE dateTime>? AND dateTime <= ?) AND
dateTime>? AND dateTime <= ?;"""
t1 = time.time()
result = manager.getSql(sql_stmt, (start_ts, stop_ts, start_ts, stop_ts))
t2 = time.time()
print result, "Elapsed time for query to main archive table =%f" % (t2-t1)
######################################
#
# Use the daily summaries
#
# 0.011 for first run
# 0.00032 for second run (presumably, after caches are filled)
#
day_manager = weewx.manager.DaySummaryManager.open(database_dict)
sql_stmt = """SELECT maxtime, max FROM archive_day_outTemp WHERE
max = (SELECT MAX(max) FROM archive_day_outTemp WHERE dateTime >= ? AND dateTime <?) AND
dateTime >= ? AND dateTime < ?;"""
t1 = time.time()
result = day_manager.getSql(sql_stmt, (start_ts, stop_ts, start_ts, stop_ts))
t2 = time.time()
print result, "Elapsed time for query to daily summaries =%f" % (t2-t1)
