Difference between revisions of "Database structure"
From PhotoVoltaic Logger new generation
m (→pvlng_babelkit) |
m |
||
| (37 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | == | + | {{TOCright}} |
| + | [[Category:Internals]] | ||
| + | '''♦''' There is also a [http://pvlng.com/schema/index.html database schema] generated with [http://schemaspy.sourceforge.net schemaSpy]. | ||
| + | |||
| + | == General == | ||
| + | |||
| + | Here are the most important tables with their references: | ||
| − | == | + | |
| + | [[File:DBStructure.png|480px]] | ||
| + | |||
| + | == Channel types == | ||
| + | |||
| + | '''<big>pvlng_type</big>''' | ||
[[File:DBTableType.png]] | [[File:DBTableType.png]] | ||
| − | == | + | == Channels == |
| + | |||
| + | '''<big>pvlng_channel</big>''' | ||
[[File:DBTableChannel.png]] | [[File:DBTableChannel.png]] | ||
| − | == | + | == Channel hierarchy == |
| + | |||
| + | '''<big>pvlng_tree</big>''' | ||
The channel tree is organized in a nested set structure. | The channel tree is organized in a nested set structure. | ||
| − | <blockquote>The nested set model is a particular technique for representing nested sets (also known as trees or hierarchies) in relational databases. | + | <blockquote>The nested set model is a particular technique for representing nested sets (also known as trees or hierarchies) in relational databases.<br /> |
| + | The nested set model is to number the nodes according to a tree traversal, which visits each node twice, assigning numbers in the order of visiting, and at both visits. This leaves two numbers for each node, which are stored as two attributes. Querying becomes inexpensive: hierarchy membership can be tested by comparing these numbers. Updating requires renumbering and is therefore expensive. | ||
<cite>[[Wikipedia:Nested set model]]</cite></blockquote> | <cite>[[Wikipedia:Nested set model]]</cite></blockquote> | ||
| Line 21: | Line 37: | ||
[[File:DBTableTree.png]] | [[File:DBTableTree.png]] | ||
| − | == pvlng_reading_num / | + | == Measuring data storage == |
| + | |||
| + | '''<big>pvlng_reading_num, pvlng_reading_str</big>''' | ||
[[File:DBTableReadingNum.png]] [[File:DBTableReadingStr.png]] | [[File:DBTableReadingNum.png]] [[File:DBTableReadingStr.png]] | ||
| − | |||
The difference between the reading tables is only the data format for reading values. | The difference between the reading tables is only the data format for reading values. | ||
| Line 32: | Line 49: | ||
It was tested and have significant more performance for consolidated data readouts. | It was tested and have significant more performance for consolidated data readouts. | ||
| − | == | + | == Dashboards == |
| + | |||
| + | '''<big>pvlng_dashboard</big>''' | ||
| − | + | [[File:DBTableDashboard.png]] | |
| + | == Tariffs == | ||
| + | |||
| + | '''<big>pvlng_tariff</big>''' | ||
| + | |||
| + | [[File:DBTableTariff.png]] | ||
| + | |||
| + | '''<big>pvlng_tariff_date</big>''' | ||
| + | |||
| + | [[File:DBTableTariffDate.png]] | ||
| + | |||
| + | '''<big>pvlng_tariff_time</big>''' | ||
| + | |||
| + | [[File:DBTableTariffTime.png]] | ||
| + | |||
| + | == Charts == | ||
| + | |||
| + | '''<big>pvlng_view</big>''' | ||
| + | |||
| + | [[File:DBTableView.png]] | ||
| + | |||
| + | == Translations == | ||
| + | |||
| + | '''<big>pvlng_babelkit</big>''' | ||
| + | |||
| + | Here are stored all translations for the [[:Category:Web frontend|web frontend]] based on the "BabelKit - Multilingual Code Description Lookup Table" <ref>http://www.webbysoft.com/babelkit/</ref> project. | ||
[[File:DBTableBabelkit.png]] | [[File:DBTableBabelkit.png]] | ||
| − | + | ''The "changed" column is used to detect recently changed translations for upgrade SQLs'' | |
| + | |||
| + | ---- | ||
| + | <references/> | ||
Latest revision as of 21:55, 25 March 2015
♦ There is also a database schema generated with schemaSpy.
General
Here are the most important tables with their references:
Channel types
pvlng_type
Channels
pvlng_channel
Channel hierarchy
pvlng_tree
The channel tree is organized in a nested set structure.
The nested set model is a particular technique for representing nested sets (also known as trees or hierarchies) in relational databases.
The nested set model is to number the nodes according to a tree traversal, which visits each node twice, assigning numbers in the order of visiting, and at both visits. This leaves two numbers for each node, which are stored as two attributes. Querying becomes inexpensive: hierarchy membership can be tested by comparing these numbers. Updating requires renumbering and is therefore expensive.
Wikipedia:Nested set model
More theory about nested sets.
Measuring data storage
pvlng_reading_num, pvlng_reading_str
The difference between the reading tables is only the data format for reading values.
The timestamps are not stored as "normal" timestamp type, but as unsigned integer.
It was tested and have significant more performance for consolidated data readouts.
Dashboards
pvlng_dashboard
Tariffs
pvlng_tariff
pvlng_tariff_date
pvlng_tariff_time
Charts
pvlng_view
Translations
pvlng_babelkit
Here are stored all translations for the web frontend based on the "BabelKit - Multilingual Code Description Lookup Table" [1] project.
The "changed" column is used to detect recently changed translations for upgrade SQLs
