SQL Search Facilities

General:

The SQL Search Facilities are based on the availability of full SQL support in NexusDB in version 2.00 and upwards. Since this database engine is now being fully implemented in TurboLog 4 as well, we have created an intuitive interface for exploitation of its benefits to be used by all our users.

SQL is an extremely powerful search facility. There is virtually nothing in the logbook which cannot be retrieved. This applies in particular for partial strings in any field, most complex combinations of fields in the same database table and paramount: Even recursive searches among fields across all available tables in TurboLog 4. For the time being true SQL queries can be applied to the following databases:

•Logbook

•DXCC

•Notepad.

Unfortunately, there is just nothing at the expense of nothing. The general result is: The more sophisticated the search task the more complex the SQL script will need to be. SQL is a world of itself and widely used in most professional database applications. Syntax descriptions and presentations of all available language elements in SQL typically cover a manual of 500 to 600 pages. The deserving user is referred to the complete set of documentation provided by NexusDB at:

http://www.nexusdb.com/onlinemanualv2/SQLreference/index.html

Necessarily, the following sections can only present a subset of the magnitude of features available under the SQL regime. Our intention,

however, is to provide a user friendly interface along with a number of illustrating smashing examples in order to demonstrate its use and present an impression of the unique and unprecedented capabilities.

For ease of operation we have split the SQL Search Facilities into:

•Assisted SQL and

•Free SQL

Both facilities are hosted by the same framework, along with the Simple Filter. The Assisted SQL Facility is intended to support the user with more hints and predefined language elements. This should help pointing to the correct direction and assure the corresponding field names being supplied on the group box.

Free SQL does not anticipate any standard table selection nor its associated field names. The query designer will be free but responsible on its own for synthesizing a correct choice of tables and field names.

We strongly recommend to familiarize yourself with the Simple Filtering before you approach the SQL Search Facilities.

Furthermore, the Assisted SQL layout should be used at the beginning, i.e. for designing short queries and making the first steps.  Finally, the most complex and by the same token most powerful SQL queries may be tried. They need to be run under the Free SQL regime because any sorting algorithm for the query sections will finally fail. You should always start by trying the sample scripts in order to become acquainted to the syntax of language elements and to derive your own queries from the examples given.

How it works:

The SQL Search Facilities are using the same interface as presented for the Simple Filter. The interface can be entered by Right Mouse Key Clicking into the logbook window or into the DXCC or the Notepad table while they have been opened by the editor, respectively. Likewise: All logbook filtering can also be started from the logging window's main menu, item Filter. There are two entries on this menu and/or the context menu being relevant for our purposes:

Figure 176:  SQL Filter Menu

While Cancel Filter will only become active after a filter has been applied the Filter item is ready for use. Selecting the Filter item will pop up the following frame:

Figure 177:  Assisted SQL:   Query Design and Control Frame

As will be noticed the SQL frame is a more refined derivative of the Simple Filter frame. Since the script Find_MM_Calls was created under the Assisted SQL regime it is displayed accordingly. A reloading operation will automatically open the design frame set to the native mode of the script.

The example script shown will find all QSOs in the log with Calls ending .../mm  which are "maritime mobile" operations. As indicated in the paragraph on Simple Filter this sort of query that depends on a search string starting with a wildcard character ... as simple as it appears to be ... is computationally quite demanding and can only be performed under the SQL regime.

Likewise, in case of a more complex query the Free SQL layout will show up as follows:

Figure 178:  Free SQL:   Query Design and Control Frame

Please note: 

The Free QSL layout will automatically be displayed if the complexity of the query is such as to require a notation of more than 5 lines of text. 

The frames consist of the following elements:

Boxes:

Fieldnames:

For ease of synthesizing a new SQL script all available field names of the particular database table (  e.g.: QSO_Table, if selected ) are automatically displayed and can be dragged from the box and dropped at cursor position into the Condition box ( mouse: Drag & Drop action ).

Keywords:

This box displays a selection of the most common SQL operators which can be used for script design. These keywords can be dragged and dropped into the Condition box as well. The available keywords are by no means restricted to the selection shown. The advanced user may apply other suitable SQL language elements. Just a glance on what can be done is outlined in the examples presented below.

Field Selection:

This box which is available in Assisted SQL makes sure to correctly address the database table to work with. Thus, its content will automatically anticipate the default table name (... the one you highlighted and where you came from ). The default table name is denoted $TABLENAME and can be inserted by dragging  from the list of Keywords.

Condition:

This is the box to synthesize your SQL query. You can type the script, load it from a file for a repeated execution or modification or compile it by Drag & Drop action. SQL is very sensitive to a correct notation.

Please note: Fieldnames have to be enclosed in "....", while search strings need to be represented as ' ...' . If you are not an experienced SQL user we recommend to start your own queries from an existing example and to proceed in very small steps. The SQL error messages are not very meaningful. Unfortunately there is nothing we can do about it since they are internally generated by the SQL engine in NexusDB.

Sort Order:

You can edit the sort order of your search results in this box. The sort order is determined by the fieldnames specified. The Sort Order box can be filled by Drag & Drop action. The default sorting according to the sort terms will be ascending.

Buttons/Radio Buttons:

Load Script:

Clicking the Load Script button or pressing the short cut Alt+L will open the Save/Load control box as depicted below.

Save Script:

Clicking the Save Script button or pressing the short cut Alt+S will open the Save/Load control box as depicted below.

New Script:

Clicking the New Script button or pressing the short cut Alt+N will delete the contents of the Condition and Sort Order box and  clear the boxes for editing a new script.

Apply:

Clicking the Apply button or pressing the short cut Alt+A will start the SQL query.

Cancel:

Clicking the Cancel button or pressing the short cut Alt+C will close the SQL Query Design Frame without any further actions.

Help:

Clicking the Help button will display this particular help topic.

Case Sensitive:

This radio button will allow you to execute queries which depend on case sensitive notations. Furthermore, it controls the use of the "Upper" statement which assures matching of strings to their notation in the log. SQL is case sensitive as such!

Free SQL:

This query layout should be selected in case of a complex scripts which require a lot of editing and a non restricted use of syntax elements and controls. It will automatically be selected if a particular script consists of more than 5 lines of text.

Assisted SQL:

This query layout should be preferred for all scripts which require the use of the main database control statement: SELECT * FROM "$TABLENAME"  AS QSO_Table ... . It will automatically be selected if a particular script that is loaded consists of 5 or less lines of text. A set of powerful example scripts ... ready for instant use... is available in TurboLog 4 from the drop down box of the Save/Load dialogue frame.

Simple Filter:

This is a layout for applying simple filtering as described in the paragraph on Simple Filter.

Descending:

Checking this radio button will reverse the sort order which is ascending by default.

In case you are running a complex SQL query on a slow machine your query may run into a database timeout problem. The timeout can be adjusted as to fit your problem by editing its default value in Basic Properties.

Press the Cancel button in order to leave the SQL Query Design and Control frame without making changes or saving your script.

Please select the Cancel Filter item from the menu in order to reset the data grids to their default layouts.

If you want to save your script or if you want to load another script from file please read the next section.

Save and Load operations:

Of course you can save your favorite SQL scripts and later reload them for a new query. Clicking the Save Script button in the SQL Query Design and Control frame or pressing the short cut Alt+S will surface the following box:

Figure 179:  Save/Load SQL Script Box

This versatile control box exhibits a number of very useful features. It consists of the following elements:

Boxes:

Scripts:

This box contains names of available scripts which have been saved before. Right below is the entry box for the name of a script that you want to save. Do not forget to specify a name here. This entry box will also display any selected script in case of a Load action.

Remarks:

This is a simple memo field. You can enter a description in order to help you remembering the rationale of that particular script. You are allowed to use free text.

Filter Script:

This box shows the particular SQL Script selected for save or load action.

Buttons:

All buttons are under context control. Those being grayed are not available for the purpose you decided before you entered the Save/Load control box.

Save

Clicking the Save button or pressing the short cut Alt+S will save your script and remarks in a compound file. You will be prompted if you are going to overwrite an existing file and in case you have made changes on the existing file.

Delete

Clicking the Delete button or pressing the short cut Alt+D will delete the selected script file after you have been prompted for this operation.

Export to..

Clicking the Export to.. button or pressing the short cut Alt+E will open another file dialogue and allow you to save the SQL script ( without remarks! ) in a directory of your choice. The default, however, is on the ...\SQL SCRIPTS sub directory of TurboLog 4.

Help

Clicking the Help button will display this particular help topic.

Cancel

Clicking the Cancel button or pressing the short cut Alt+C will close the control box without any further actions.

If available:

Import from ..

Clicking the Import from .. button or pressing the short cut Alt+I will open another file dialogue and allow you to import a foreign script ( without remarks! ) from a directory of your choice. This will be of particular interest for importing complex SQL scripts.

Load

Clicking the Load button or pressing the short cut Alt+L will load a selected script into the Filter Design Frame and close the Save/Load control box.Likewise, you can double-left-click on a selected script in order to apply it instantly.

Rename

Clicking the Rename button or pressing the short cut Alt+R will allow you to rename a selected file, if appropriate.

Some Sound Example Scripts:

1. Example:

Task: Find all records with more than two entries in the USERKEY field:

Figure 180:  Find >1 entries in Userkey field

2. Example:

Task: Find unique Calls in the log, regardless of Mode:

Figure 181:   Find Unique Calls

3. Example:

Task: Find unique Calls in the log on CW:

Figure 182:   Find Unique Calls on CW

4. Example:

Task: Find notorious No-QSLers:

Figure 183:   Find No-QSLers

5. Example:

Task: Find first QSOs with DXCC entity regardless of further conditions:

Figure 184:   Find First QSOs

This is a true expert's design, just in order to demonstrate what can be done.

6. Example:

Task: Find QSOs between two dates:

Figure 185:   Find QSOs between Dates

A problem arises on queries which include dates. Unfortunately DATE is a keyword in SQL and in TurboLog 4's QSO table. However, this can easily be overcome by a notation as shown in the example above.

Please note:

All examples shown above have specified the output grid display to be identical with respect to the fields to appear on screen as the user has specified in Customizing the Logbook Window . This is the result of the first   SELECT *   statement. Of course you can command a different grid layout to be displayed for the search results. The following statement:

Select "Countryname","Call","BAND"  from "$tablename" as QSO_table    ...

for example will produce an output of three columns, namely Countryname, Call and Band. In this case, however, you need to make sure that all three fields are among those of the original display. This is necessary in order to automatically generate the column headers. If one of the selected fields is missing an error message is shown and the query will fail. 

Active COMMANDS:

Local short cuts for SQL Script Design frame:

Local short cuts for Save/Load control box:

CLICKABLES and HOTSPOTS:

Drag & Drop

Double-left-click        will apply selected script instantly

BUTTONS:

Apply

Load Script

Save Script

New Script

Cancel

Help

Export to ..

Import from ..

Delete

Load

Save

Rename

This topic was last edited on  Monday, 03-Jul-2023,  at  23:57