SQLite Analyzer sticks to the MDI interface. In the main window you can find the main menu of the editor, the toolbar, the Database Browser and the working area of the editor (Fig. 2.1). The main menu contains the main groups of commands. The most frequently used functions are displayed on the toolbar. The database tree is a hierarchic representation of objects. It contains their list and represents embedment relations. The working area is used to create and edit scripts, create tables, modify data in them. The results of SQL query execution are also displayed here.
Fig. 2.1. The main window of SQLite Analyzer
The main menu of the editor is designed in the standard style of the Windows interface. It contains common menus with commands, such as File, Edit, Search, Options, Help, and those specific for SQLite Analyzer: Database, Diagram, Query.
The File menu contains four groups of commands (Fig. 2.2): managing windows, managing scripts, managing databases and exiting the editor. The most frequently used commands have hot keys assigned to them. They can be used to execute the corresponding commands without opening the menu. If there is a toolbar button for some command, this button is displayed right before the command.
The group of commands for managing windows is used to create a new window in the editor area (New Window) and close the active one (Close Window), i.e. the window active at the moment.
Scripts are managed by executing commands for editing and executing an existing script (Open), saving a script or the results of executing an SQL query that are displayed in the active window (Save) and creating a new version of the script with another name or location (Save as…).
An SQLite database is stored in a single file. Ways of creating it and working with it are similar to working with regular files. The command New Database Registration… is similar to the command for opening files. The command opens the standard Browse dialog box that is used to select the location and name of the database file. SQLite Analyzer remembers the location and name of a file until they are changed or deleted by the following two commands. Edit Database Registration properties… changes the registration parameters – the location and name of the database file. Delete Database Registration removes the database from the list of displayed objects. The database itself is not removed from the disk.
The Exit command closes SQLite Analyzer.
Fig. 2.2. File menu
The Edit menu contains two groups of commands: undoing changes and managing selected blocks (Fig. 2.3).
SQLite allows you to undo multiple changes in scripts (Undo) and get all the undone changes back (Redo). The number of undo operations is unlimited. The Redo command works until the content of the script is back to its original state, i.e. the state before the first Undo command.
Operations with blocks allow you to:
Fig. 2.3. Edit menu
This set of commands allows you to quickly search for the specified text and replace it with other text if necessary (Fig. 2.4).
Fig. 2.4. Search menu
Find opens the window for entering the search parameters. It includes the search string, direction and text comparison rules (word completeness, case sensibility). Without closing this dialog box you can continue searching in the specified direction. The «Only whole word» parameter is convenient when you are searching for keywords in a script since it excludes intermediate stops at unrelevant search results.
Find next and Find Previous allow you to search for the string specified last in the Find dialog box without opening the Find dialog box and entering parameters in it. The first command searches down while the second one searches up.
Replace is used to replace one string with another.
You should use the dialog box to enter the old and new strings, select comparison
rules and execute one of the commands by clicking the corresponding button.
The menu for working with databases is used to work with tools for managing database objects of any level.
The first two commands are used to create a database (Create Database) and check its integrity (Check Integrity).
Create Database creates an SQLite file with the specified name and location on the disk. The file is used for storing database objects. The file name is the name of the database. For you to be able to work with a new database, it should be connected to SQLite Analyzer. It is also possible to establish a connection from the context menu in the object tree (see 3.1 Databases).
Commands for working with tables are used to manage the content and structure of tables as well as the way data stored in them is displayed.
New Table and Design Table open a graphical editor for creating and (or) editing the structure of a table. For details, see below 3.2 Tables. Delete Table/View is used to delete a table or a view. Open Table opens the data from a table (view) in the working window of the editor.
Some commands from the table management group may be unavailable. Only creating a new table is always available. Commands for working with the structure and displaying data are active when the current selection in the object tree is within a table (view). The command for deleting is available only when the current item in the object tree is the name of a table (view).
Two commands for managing indexes (Manage Indexes and Manage Triggers) open the wizard for working with indexes and triggers respectively. Working with them is described below (see 3.3 Indexes, 3.4 Triggers).
You can also use the Database menu to open the commands for creating scripts automatically for objects selected in the tree (Script Object to New Window As and Script Object to Clipboard As). The first command opens the script in a separate window while the second one copies it into the clipboard. To paste this script into any window of the editor, just use the Paste command from the Edit menu or the Ctrl+V key combination.
Several script types can be used for objects: Select, Insert, Update, Delete, Drop. Scripts are commands written with the SQLite syntax. Depending on the type, either they can be ready to be executed or they can be half-prepared scripts that should be supplemented before their execution.
Fig. 2.7. Database menu
The Diagram menu is not available yet. It will be available in one of future versions. The editor of ER models is being developed. It will allow you to create database models and perform two-way synchronization.
Commands for working with SQL queries are divided into two groups: managing the process of executing queries and managing the way their results are displayed (Fig. 2.8).
The first group includes:
The second group includes:
Fig. 2.8. Query menu
The options of the editor determine the parameters of displaying the working
window of SQLite Analyzer, displaying scripts and query results in it and rules
for sorting data. The options are grouped by their purpose. One group of options
is displayed and specified on each tab of the dialog box.
The parameters of the working window of the editor are specified on the SQL
Editor tab (Fig. 2.9). Here you can select (clear) the checkboxes enabling the
right margin and the gutter. The right margin is useful to observe the length
of lines in large scripts. It improves their visualization. These parameters
are set to 40 and 20 respectively in Fig. 2.10.
Fig. 2.9. Editor options
Fig. 2.10. Visible gutter (20 mm) and right margin (40 characters)
The Syntax Highlight tab is used to set the rules of displaying and highlighting various types of text used in the process of creating scripts (Fig. 2.11). You can configure the way text is displayed to your liking.
There are several types of text. Each of them has its own rules of displaying.
Fig. 2.11. Two variants of displaying a script
Each type of text has its Foreground color and Background color. You can also specify the type of fonts (Text attributes: Bold, Italic, Underline).
You can specify the font and background color for the area with the results of SQL queries on the Results tab (Fig. 2.12).
Fig. 2.12. The parameters of displaying results
The standard Windows dialog box is used to select the font.
Tables Editing determines the rules for updating data while editing tables. SQLite Analyzer uses three types of updates:
The first method (Nothing) can be used when there are no triggers in the table being edited and the database is exclusive. Changes in records you are editing are visible on the screen. That is why no data needs to be updated.
Selecting the second and the third methods slows down working
with large tables. The time of reading a file with a small table is unnoticeable
so the method selected when the database is used exclusively does not matter.
If a lot of users are simultaneously connected to the database, it is possible
to see changes made by other users only when the Completely update
method is used. When a single user works with the database, the most appropriate
update method is Only the changed record. It makes the editor read
only modified records from the file. You should keep in mind that if built-in
triggers modify not only one record where data is changed, but other records
in the table as well, the Completely method should be used.
Fig. 2.14. Rules for updating data while editing a table
The option Custom Collations determines relations between a custom collation and a collation built into SQLite (Fig. 2.15).
Fig. 2.15. Specifying relations between the custom and built-in collations
A collation is a function determining the rules for sorting lines. The task comes down to finding out which of two characters is comes first. Suppose Russian text is stored in the database. If you sort a sample by a "Russian" field for which an English collation is specified, sorting will be incorrect. That is because the code of the letter "Z" is 48, while the code of "R" is 56 (in Russian charset). So SQLite will put the letter "Z" first and only after that it will put "R". But they have a different order in the Russian alphabet. That is why you need to specify a collation, i.e. a function that will allow SQLite to determine which character comes first from the point of view of the Russian (Chinese, Spanish...) language.
That is why you should specify the type of collation while developing the structures
of tables. There are collations in SQLite for most languages. But the developed
database can be used in other countries and in other languages. These rules
are not applicable to them. The variant of editing the collation – going
through all tables and changing it into the one needed – can be omitted,
it is not applicable.
But SQLite has functions that allow you to specify custom collations for the
current connection to the database. So it is possible to specify MY_COLLATION
while creating a table. And the collation of the specific language for MY_COLLATION
is specified in the SQLite Analyzer dialog box. With this approach, when the
user speaking some other language starts working with the database, all he needs
to do is specify his collation for MY_COLLATION in the editor options. It is
enough to make the output from all tables correct.
It is possible to specify any number of collations on the Collation tab. It
is important for all collations used in the process of designing the structure
of tables either to have one of the standard SQLite collations (binary, revers,
nocase) or to be specified in this collation list.
The menu for managing windows contains commands arranging windows in the working
area of the editor and the list of windows opened in the editor.
Cascade arranges windows so that the title of each
next window is positioned under the title of the previous one.
Tile Horizontally and Tile Vertically
turn the editor area into a grid with equal cells and place open windows in
them. Minimize All minimizes all windows and places
their titles at the bottom of the working area of the editor.
The list of windows contains windows open at the moment. It is possible to select any window from the list to make it active.
Fig. 2.16. Arrangement rules and selecting the active window
The Help menu is used to solve three tasks: getting help about working with the editor and about the syntax of SQL commands, registering the program and contacting the developer (Fig. 2.17. Help and contacts).
Fig. 2.17. Help and contacts
Help and SQL Help open an electronic document with the navigation and description areas (Fig. 2.18. Help about the syntax of commands).
Fig. 2.18. Help about the syntax of commands
The registration commands open the instruction on how to register the editor (How to Register) and the registration dialog box (Enter S/N).
The toolbar contain buttons for executing the most frequently used commands (Fig. 2.19. Toolbar):
Fig. 2.19. Toolbar
The Database Browser displays registered databases and all their objects in the form of a tree (Fig. 2.20. Displaying objects). Each database has its tree, the top node of which contains the name of the database. Any other object has its own node as well. The structure of the tree reflects the structure of the database. On the first level of the list there are tables and views, while table elements (columns, indexes, triggers) are displayed on the second level. Columns are pending nodes so their parameters are displayed in the list. In their turn, indexes may include the list of columns being used that is why they are detailed further in the tree.
By expanding or collapsing the nodes, you can control how detailed the information is. For example, the table customer is displayed in the most detailed way in Fig. 2.20. Displaying objects. Only names are displayed for other tables. While the database SB is collapsed altogether.
Fig. 2.20. Displaying objects
You can manage objects in this area using the context menu. You can create, delete, edit, view objects, generate scripts for them, etc. (Fig. 2.21. Context menu in the object tree). Commands available in the context menu depend on the type of the object for which this menu is opened. For example, when you open the context menu for a table column (field) commands for changing the database registration parameters are unavailable.
The detailed description of commands from the context menu is given in the
following sections.
Fig. 2.21. Context menu in the object tree
| The CHM file was converted to HTML by chm2web software. |