Administering databases

Databases

Creating, deleting

The wizard for creating a new database is opened from the main menu of the editor (Database\Create Database). You should specify the path to the new database and the name of its file in the wizard window. As a result of this command, the editor creates the specified file containing only the header with the SQLite structure so far. The file is filled later by creating tables, indexes and entering data into tables.

The created database is not yet registered in the editor. The registration process is described in the next section

Fig. 3.1. Creating a database

Registering a database

There can be numerous SQLite databases on the comouter. But not all of them are necessary for your current work. The databases you are working with are registered in the editor for them to be always at hand. Database registration does not result in locking the file or any other registration-related consumption of computer resources. SQLite Analyzer just gets the possibility to quickly access the objects of the registered database.

To register a database in SQLite Analyzer, you should:


Fig. 3.2. Registering and unregistering a database

Fig. 3.3. Specifying the location of the database

To make a database in SQLite Analyzer unregistered, you should:

Just in case SQLite Analyzer makes sure that unregistering a database is not accidental (Fig. 3.4. Unregistering a database)

Fig. 3.4. Unregistering a database

The situation when the file of a database registered in the editor is moved to another location or renamed is also possible. In this case you should change the database registration properties. To do it, you should also use the context menu (Fig. 3.2. Registering and unregistering a database) and select Edit Database Registration Properties. You should specify the new location and name of the database file in the new dialog box.

Tables

Creating and deleting tables

To create a table, select New Table (Fig. 3.5. Creating a table) in the context menu. The table is created in the database the mouse pointer is over at the moment when you open the context menu.

Fig. 3.5. Creating a table

The editor asks the name of the table you are creating (Fig. 3.6. Entering the name of a table) and switches to designing the structure of the table after it is specified. The detailed description of the designing process is given in the next section.

Fig. 3.6. Entering the name of a table

A table is deleted by selecting Delete Table/View from the same context menu. When this command is selected, the active record in the Database Browser must be the table you are deleted. The editor protects you against an accidental deletion (Fig. 3.7. Deleting a table)

Fig. 3.7. Deleting a table

Table structure

The structure is specified either when a new table is created or when you select Design Table in the Database Browser. The structure of a table is a set of its fields. Each of them has certain properties (Fig. 3.8. Structure window).

Fig. 3.8. Structure window

The set of columns is created and the result is saved with the help of the commands on the toolbar of the window or by selecting commands from the context menu (Fig. 3.9. Context menu and local toolbar).

Fig. 3.9. Context menu and local toolbar

The main field properties are displayed in the table for editing the structure. The following columns are used for that:

Using the «Key» icon, you can include the set of fields into the main index. The order of fields in the index corresponds to the order they are marked with this icon.
A special window is used to describe all field properties. The command Column Properties opens it (Fig. 3.10. Field properties). The following field properties are available:


Fig. 3.10. Field properties

Column Name is later used in SQL queries for sampling and modifying data. The name of a column must be unique within one table. It can contain only letters and digits.

Column type determines the format and the size of data stored in this table column. Data type can be selected in a number of ways:

Fig. 3.11. Selecting the data type for a column

Default value – this value is given to a field when a record is added if it is not specified in the list of fields. The default value is assigned if the Allow Null checkbox is cleared.

Collation determines the sorting rules. By default, the method specified in the editor options is applied (see 2.2.7 Options). If necessary, it can be another value assigned by selecting from available variants enumerated in the drop-down list of this field.

Nulls is a permission of a null value. The selected Allow Null checkbox allows you to create records with null values while the cleared checkbox forbids it. An attempt to assign the null value to a field that has this checkbox cleared causes an error. The method of handling the error is determined by the value of the Conflict clause field.

Unique enables or disables the uniqueness property. The selected Unique checkbox enables the mechanism of controlling the uniqueness of the value in this field within the entire table. If at least two records with the same value in this field appear in the table, a conflict occurs. It is solved using the method specified in the Conflict clause field.

Primary key is one or a list of fields the combination of values of which must be unique within the entire table. When a Primary key is used, the editor automatically creates the main index that includes all fields enumerated in the Primary key.

It is also used to sort table data when it is displayed on the screen. The sorting method can be specified in the Sort order field – either ascending or descending.
The Primary key controls the uniqueness of the combination for the values of all columns included in the Primary key. If records with the same combination of the key values appear, a conflict occurs. It is solved using the method specified in the Conflict clause field.

For the main key you can specify the value of the automatic increment specified in the Auto increment field. Each time a record is added to the table, its value is increased by one. This parameter can be specified only for fields of the INTEGER type.

Check specifies rules the value of the column must meet. When these rules are broken, a conflict occurs. The rules for handling it are specified in the Conflict clause field.

Conflict Clause determines the variant of handing conflicts occurring in those fields that have various constraints specified for them. There are several ways to handle a conflict:

RollBack cancels the transaction within which a conflict occurs. Abort exits the program. Fail causes an error event with a certain code that can be later processed by the software developer. Ignore ignores the event as if there is no conflict. Replace replaces the old data with the new data.

A table is saved in a few steps. If the table already exists, all data from it is copied into a temporary table. After that the old table is deleted and a new one is created with the modifications in its structure taken into account. Data from the temporary table are moved to the new table. If the copy operation is successful, the temporary table is deleted and existing indexes and triggers are created anew. In case an error occurs at any step, the old table in its initial form is restored.
The user can control all these operations. Before a table is saved, the script carrying out all these operations is generated. This script is displayed in a special window. An experienced developer can modify this script the way he needs it. There is also a checkbox allowing you to roll back all changes in case of an error in that window. That is, suppose you modify the script, it turns out that there is an error in it - the database will remain unchanged since all changes are cancelled.

Indexes

Creating indexes

The editor creates part of indexes automatically in case the «Primary key» or «Unique» proeprties are used in the description of fields. Such indexes cannot be deleted until these properties are diabled.

It is also possible to create additional indexes. The index management wizard is used for that. It can be opened from the context menu of the Database Browser (Fig. 3.12. Opening the index management wizard) or from the main menu of the editor (Database/Manage Indexes).

Fig. 3.12. Opening the index management wizard

The working window of the index wizard contains the name of the table indexes are created for, the list of indexes and control elements (Fig. 3.13. Forming the list of indexes).

Any table from the current database is specified in the Table field. The name of the table can be typed or selected from the drop-down list. A new index is added by either entering its name into the next empty line or using the New button. An index is deleted with the command Delete and the wizard is exited with the help of the Close button.

Fig. 3.13. Forming the list of indexes

The Edit command open a dialog box where there are special fields and working modes for entering each property (Fig. 3.14. Index fields and their proeprties). Fields included into an index are selected in the Column column. To change the order of columns in the index, you can use the Up / Down buttons. They move the current column up and down respectively.

The sorting methods is one of the column options (Asc / Desc.). Its default value is Asc. The sorting method labeled Collation should be specified for text fields. By default, the sorting method is specified the same as the one specified in the structure of the table for this field.

You can assign the Unique property to an index. In this case you should specify the method for solving conflicts in the On conflict field.

The OK button is used to save the index properties currently displayed, while the Cancel button cancels the changes in the index properties.

Fig. 3.14. Index fields and their proeprties

Triggers

Creating triggers

You can specify triggers for each table in an SQLite database. Triggers are managed with the help of a wizard opened from the context menu of the Database Browser (Fig. 3.15. Opening the wizard for managing triggers) or from the main menu of the editor (Database/Manage Triggers).

Fig. 3.15. Opening the wizard for managing triggers

The window of the trigger wizard contains the name of the table they are used for, the list of triggers and control elements (Fig. 3.16. Adding triggers).

Any table from the current database is specified in the Table field. The name of the table can be typed or selected from the drop-down list. A new trigger is added by either entering its name into the next empty line or using the New button. A trigger is deleted with the command Delete and the wizard is exited with the help of the Close button.

Fig. 3.16. Adding triggers

The Edit command opens a dialog box with special fields for entering each property, as well as with a field for entering the SQL text of the trigger (Fig. 3.17. Creating the SQL text of a trigger ).

Trigger properties

While describing trigger proeprties, you should specify its name, type, event, the list of columns for the Update of event and also its SQL text (Fig. 3.17. Creating the SQL text of a trigger ).

Fig. 3.17. Creating the SQL text of a trigger

The rules for naming triggers are the same as for any SQLite keywords.

Two types of triggers are used: Before and After. The first one is activated before a record is modified, while the second one is activated after that.

Database events that can activate a trigger:

When using the Update of event, you should select those table fields from the list in the Columns field, modifying which activates the triggers.

The SQL text of a trigger is entered into the Trigger Action field. It must meet all rules of the SQLite syntax, begin with the Begin keyword and end with the End keyword.

The CHM file was converted to HTML by chm2web software.