3.15  MySQL command and scripts.   MySQL Front


MySQL database access

Please download and install the database editor / manager application:  MySQL Front.


Note that MySQL Front will, by default present all records in a database table.
ie: showing all records for all configured rigs. Sorting by rig name facilitates
presentation by rig name.

piWebCat's editor only shows records for the selected rig.


I have extensively used MySQL Front but their are alternative database editor / toolkits.
HeidiSQL is a good alternative.


External database access uses:
     host = IP address (eg 192.168.1.113)   port = 3306   
     user = piwebcat     password = feline   database = radios


Internal database access from piWebCAT is specified in webserver file:

                                          /var/www/html/cat/phpfiles/wcmysql.php

and has the same user name and password. (host = localhost because internal access)


//  MYSQL access

$DbServer = 'localhost';

$DbUser = 'piwebcat';

$DbPw = 'feline';

$Database = 'radios';


MySQL scripts  - these are listed in the the following section 3.15


MySQL database creation, configuration, data read and write and other operations all

use text-based MySQL commands.
These are the same whether used internally by piWebCAT or for external access by
MySQL Front or other SQL based programs.

External scripts often contain multiple command lines.

All commands must be terminated with semi-colon.


Some typical MySQL command lines are shown below.


UPDATE buttons SET rig = "FT2000" WHERE rig = "FTdx101D";

This, if applied to all relevant tables would be used to change he name of a rig configuration.

eg: when adapting my FTdx101D configuration for an FT2000.


DELETE FROM catcodesdciv WHERE rig = "IC7000";

This if applied to all relevant tables would be used to remove IC7000 from the database.  


UPDATE catcodeshl SET readmask = REPLACE(readmask, "Main","VFOA")
                                                       WHERE rig = "FTdx101D-H";

This selectively acts on records in the catcodeshl table with rig = "Ftdx101D".

It changes all occurrences of "Main" in the readmask field to "VFOA".

 

DROP TABLE IF EXISTS `buttons`;

This will completely remove the table buttons thereby damaging the configuration for all rigs
using table buttons, but not those using buttonsciv (Icom) or buttonshl (Hamlib connected).


Running scripts in MySQL Front.


On connecting to the radios database we have:

Left mouse click on the database name (radios) displays the tables as shown.

Left mouse click on a table name will display its structure (headed: Object browser) or its data (headed Data browser).


Right mouse click gives a list of options: Copy, New, Delete, Empty, Rename, Properties  and also Export and Import which can be used for backup and restore.


Backing up the whole database

In the image left, I right-clicked on the database name: radios and the left-clicked export.

There are a number of export options.

The SQL option should be used for backup.


After selecting SQL you specify a filename and location and then finally there is the option box below.



These are export options.


The selected options are appropriate for backup.


If you subsequently import the exported SQL file, then the whole database (including your station log) will be deleted and the regenerated from the saved SQL file.


Selective table backup

If, for example, you wanted to preserve the station log file separately, then simply perform the above process starting with right-click on the log table name in the left column.

The resulting file can then be used to selectively restore the log.


Note that any table can be exported to an excel file from MySQL or from the piWebCAT editor.


Examine a backup script   -  Use Notepad


You will see that the section for each table consists of:

  • DROP TABLE IF EXISTS 'buttons';
  • CREATE TABLE 'buttons' ( ....   followed by the table structure
  • INSERT INTO TBALE 'buttons' (.... followed by all the data


Using MySQL Front's SQL tab  


MySQL Front has three tabs at the top of the page:

  • Object browser        The structure (metadata) of the selected database table (R -mouse for properties, edit etc)
  • Data browser        A spreadsheet-like presentation of the data - with editing capability
  • SQL Editor                A page to enter individual MySQL commands (terminated in a semicolon)
                           or multiple commands.
                           The commands apply to the whole database (not just the selected table)


In the example below, a simple MySQL command has been typed:   delete from log;
This clears data from the log table (but not the structure = metadata)
I use it to clear my log before generating an SD card for distribution.

However, before doing this I must save my log as follows:          
     Expand the radios database on the left.
    R mouse click the log table item and then click Export   - SQL file and specify a filename.

The script is executed by clicking the button.



piWebCAT SQL script library (downloadable)


The SQL text below was inserted by R mouse - Paste from file.
The file used was : Rename-rig_Hamlib.sql.
This is in the small library of SQL file that I supply. It does what it says.

You need to edit the first two (SET) lines to contain the correct old and new rig names.

Note that in MySQL syntax, @source_rigname and and @new_rigname are variables that are loaded with
the name and then are used in the eight UPDATE commands.

The script is executed by clicking the button.  This runs all ten lines of code.



Adding new records


Configuration tables can be displayed and edited in piWebCAT's editor OR by MySQL Front.


piWebCAT's editors only show items in the table for the selected rig.

MySQL Front shows the whole table


Both editor systems show the Id field which is a unique identifier that cannot be duplicated.
The displays are sorted by default by Id. Changing the Id value (to a value that is not use) will, after a
refresh reorder the table.
You can sometimes use this technique to control the new record's display position.

However, the tables in MySQL Front can be sorted on a column by clicking the column header (As in Excel)


 Adding a record in MySQL Front.
Scroll to below the bottom of the table. A new record row opens up with the next sequential Id number assigned.

Enter data.  SImply click elsewhere to save.

You may then be able to move the record by changing the Id field  (but only to an Id value that is not is use)


Adding a new record in piWebCAT's editors is described in section 3.4 Editor page operation