5,838
edits
MySqlConnect: Difference between revisions
From Cumulus Wiki
Jump to navigationJump to search
m
new section at end
m (→The My SQL settings page in interface: add screenshot) |
m (new section at end) |
||
|
*Tick (value shown as 1 in Cumulus.ini)
* Don't tick (value shown as 0 in Cumulus.ini)
Please see
|-
| User=
| Database access user name, that matches with password described earlier
|}
=The MX default database tables=
Further work is needed here, but this is a quick attempt to include some notes that might help you, written because of the extensive changes in release 3.20.0
==Creation of default tables==
The settings page as described above includes the ability to issue the SQL command to your server to create whichever default table you wish.
You must define how to access your database server, enable the particular table and indicate what the table is to be called first. Then you must click '''Save settings''' so all those details are registered before the create command will work.
If a table with that name already exists, or certain other standard errors happen, MX can give you feedback. MX will also tell you when the SQL has worked, and thetable has been created.
==Modifying schema (columns in table)==
Some release announcements for MX mention that extra columns have been added to a particular table. A script may be provided (either in release announcement, or in the '''MXutils''' directory within the download, that you can run to add the extra columns. For example '''b3089-AlterMySqlTables.sql''' was provided in the '''MXutils''' directory with build 3089 to add the ''Feels Like temperature'' columns
In release 3.20.0, '''v3.20.0-AlterMySqlTables.sql''' was provided to edit the "dayfile" table and add 3 new columns (cumulative chill hours, highest 24 hour rainfall, and time when highest 24 hour rainfall ended).
Release 3.20.0 as seen on screenshot above provides buttons (under heading of '''Update database table''') for each of the default tables. The code here is rather crude, it counts the number of columns currently defined in the table (does not check what names those columns have, nor what properties those columns have) and compares against number of columns that MX can automatically insert/update at that release in that table. It assumes columns appear in same order as the fields in related file, and modifies the table to add the extra columns in the correct position to match the respective file.
==Populating rows that do not exist==
Use the utility described at [[Software#Export_To_MySQL]] for '''monthly''' or '''dayfile''' tables.
The only way to populate '''realtime''' is via the action described in settings page description above.
===Populating missing/incorrect columns in existing rows===
The options provided in the ''Data logs'' menu of the interface can be used to edit a single line of a file, and there is a setting that lets that edit also update the corresponding single row of a default table.
Whilst it may not take long to send SQL for a single line update to your database server, it will take a lot of time to select each line in file in turn, and to send the SQL to insert every line to your database server.
To update one or more columns in multiple rows, you need to generate a succession of UPDATE queries, keeping the text to send to the database server as short as possible.
One way to do this is to open the relevant file using a spreadsheet (e.g. Libre Office has a "calc" option, this is free and available for most operating systems). Create an extra column after existing columns in the spreadsheet for the "primary key", you should be able to generate this from the first one or two columns of the spreadsheet with some manipulation. Now "Hide" all the columns except those with data that you want to include in the update, and the primary key column. With some spreadsheet skills you can generate the required SQL in this format:
<pre>
UPDATE name-of-table SET first-column-name=first-row-and-first-column-value, second-column-name=first-row-and-second-column-value WHERE primary-key-column-name=first-row-primary-key-value;
UPDATE name-of-table SET first-column-name=second-row-and-first-column-value, second-column-name=second-row-and-second-column-value WHERE primary-key-column-name=second-row-primary-key-value;
</pre>
Alternatively, if you have skills in a script language like PHP Hypertext Preprocessor (PHP) you can write a small script that reads the file within a loop, picks the fields required from the line of the file, and generates the SQL (as above), and after ending loop closes file and sends the SQL to the database server.
| |||
