4.6  MySQL, MySQL Front, scripts, copying radio configurations

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 editors via wired or wifi LAN.   MySQL Front was used extensively in development..
    I strongly suggest the use of MySQL Front.


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.   There are several download sites.

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';


Cloning radios in piWebCAT


To configure piWebCAT for your radio, you have three options:

  • Add all the necessary database records 'manually' using the built in editor or MySQL Front.
    This is good learning process but a lot of work.
    There 90 buttons and 27 sliders. If you want the unused controls to be inactivated with a grayed out
    appearance, then you have to add a record for each one with:   btnno=nn, active = N.
  • Modify one of the radios in the SD card download.
  • Clone one of the radios in the SD card download.


Cloning a radio    eg:   IC7000 to IC7200


These two radios have similar sets of commands. Some CAT codes are the same. Many are different.

Modifying a clone is much easier than creating a completely new radio configuration.


For each table (eg: buttonsciv) , cloning consists of:

  • Copying all the records to a temporary table.
  • Changing the rig field from IC7000 to IC7200.
  • Deleting the Id field (primary key) because Id values must be unique when copied back to buttonsciv.
    (New, unique Id values will automatically be assigned on reinsertion on an auto-increment basis.)
  • Also, I suggest setting the metercal inval and outval fields to 0. (indicates calibration not yet done)
  • Finally, inserting the records from the temporary table into buttonsciv.


Two cloning scripts are supplied as downloads and are shown below.

The files are  RigCloneCIV.sql    and     RigCloneASCII.sql.
They are text files that can be edited using notepad or in the MySQL Front Script editor tab.


In order to use them, you must very carefully change the rig names to those of your choice.
(Please do a whole database backup before running them!!    See  MySQL Front backups )


Note that these script are not specific to MySQL Front. They would run ok using any MySQL editor program.


MySQL Front with RigCloneCIV.sql loaded


The script was loaded by using Right mouse click  >   Paste from file
(Make sure that the window is clear of scripts before doing this   .. otherwise you will run both scripts!

Clicking the  button performs the clone in a fraction of a second.




MySQL Front with RigCloneASCII.sql loaded


The script was loaded by using Right mouse click  >   Paste from file
(Make sure that the window is clear of scripts before doing this   .. otherwise you will run both scripts!

Clicking the  button performs the clone in a fraction of a second.