3.13  MySQL, MySQL Front, HeidiSQL scripts

piWebCAT stores configuration data and your station log in a MariaDB  (MySQL) database on the RPi.

The database is accessed:

  • by piWebCAT's main control window (catcontrol.php) during normal operation.
  • by piWebCAT's configuration and meter calibration editors.
  • by external MySQL editor / toolkit applications via wired or wifi LAN.  
    MySQL Front was used extensively in development.
    HeidiSQL is an alternative good database editor / toolkit which I have recently used.


The following uses MySQL Front, but the processes are achievable using HeidiSQL or other tools.


MySQL Front is a Windows front end for a MySQL database server.

It allows you to connect to database sources or import Text, SQL, MS Excel, MS Access, and ODBC files.

Dialog based data handling simplifies editing. SQL commands can also be run for automated editing tasks.


Please download and install MySQL Front on your PC.  See 14.1 File downloads

After installation:

Use   File | Open connection  > New

Give it a name-  why not piWebCAT.  Access parameters are:


Host = 192.168.1.112 - or whatever else is the IP address of your RPi

Port = 3306 - always     user = piwebcat    password = feline        database = radios


MySQL Front should connect to the database.  

Familiarise yourself with the program.

Note the object browser tab (table metadata definitions), data browser tab and SQL editor tab

Below shows the database (radios) expanded to show the list of tables and data browsing of metercal. 


Editing is directly on the cells. Moving to another cell saves the edit.

New entries can be made by scrolling down beyond the bottom of the table.
The Id field can be changed to control table ordering,  but must not be duplicated.

MySQL Front shows the whole table, whereas piWebCAT's editor shows only records for the selected radio.


 


MySQL database query language


MySQL has a large number commands.

The few discussed here are relevant to managing piWebCAT data.


Below is a MySQL query  in the Script editor tab.


Note that all queries must be terminated with a semicolon.

The script is executed by the button.   Refresh ( ) is then needed on the modified table.



An example NOT to use:   UPDATE buttons SET rig = 'FT891';

This would set every record to rig = 'FT891'   ... which you do not want.
(Manual editing to reverse this would be difficult as you have lost the rig labeling.!!)


The example in the image above is:

           

         UPDATE buttons SET rig = 'FT891' WHERE rig = 'FTdx101D';


This simply relabels all the the FTdx101D records as FT891 records.

The FTdx101D and FT891 CAT command systems are close to identical.  (2m and 70cm need adding).
So if your rig is FT891:

  • You can use piWebCAT as supplied pretending to be an FTdx101D.
  • You can then relabel the records as FT891 if you wish.


You need to perform the process for buttons, sliders, catcodes, meter, timing, lookups, rigs and metercal,
AND then change the selected radio in settings to F891 (The drop down selector would now do this.)


Note that if you are dealing with Icom radios, then the tables are:

buttonsciv, slidersciv, catcodesciv, meterciv, timing, lookups, rigs and metercal,


Deleting  records;


To delete all IC7000 records from the buttonsciv table we would use:


    DELETE FROM buttonsciv WHERE rig = 'IC7000';