The Signaling Link database

Managing the Signaling Link and Monitor Unit database

Database administration

Administration of the "Signaling Link and Monitor Unit" database of Sga-7N Monitoring system can be done by using SQLite database managing program. To start the program type the followings into a command prompt:

sqlite3.exe Sga-7N.db

After starting the program the database can be modified by using SQL commands. There are the following managing possibilities in the system:

• Listing a Monitor Unit
• Inserting a new Monitor Unit
• Deleting a Monitor Unit
• Modifying a Monitor Unit
• Listing Signaling Links
• Making relation between Monitor Units and Signaling Links

Every SQL command should be terminated by a semicolon. One command can be splitted into more rows and only the last row must be ended with semicolon. SQL strings should be written between apostrophes and not quotation marks.
Any command that changes the database (basically, any SQL command other than SELECT) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed at the conclusion of the command. (To learn more about transactions in SQLite visit this page of SQLite documentation!)

The * parameter of select is replacable by rownames separated by commas.

select sl_id, monitor_id, starttime from t_sl_monitor_rel;

Using SQLite database

For a listing of the available commands in the sqlite3 command line utility, you can enter .help. Some commands for example:

• .databases: list names and files of attached databases
• .output FILENAME: send output to FILENAME
• .output stdout: send output to the screen
• .read FILENAME: execute SQL in FILENAME
• .header ON|OFF: trun showing headers on or off
• .separator STRING: set value separator
• .show: show current value of enoviromental settings
• .mode: set the mode showing table values (for more details read "Changing Output Formats" chapter on this page!)
• .tables: list name of tables in the database
• .tables PATTERN: list tables matching the given pattern
• .read FILENAME: execute SQL in FILENAME
• .dump: convert the entire contents of a database into a single ASCII text file. This file can be converted back into a database by piping it back into sqlite3. A good way to make an archival copy of a database is this:
• .dump TABLES: only the given table(s) will be backed up
• .exit: exit this program

For further information about the command line program plese visit the sqlite3: A command-line access program for SQLite databases page.

Listing Monitor Units

Monitor Unit existing in the database can be listed by this command:

select * from t_monitor_data;

Columns in t_monitor_data table:

• monitor_id: unique identifier of the monitor in the database
• description: description or long name of a monitor, shown in client programs
• ip: IP address of the Monitor Unit, these values are generated by ServMisc modul and not in 127.0.0.1 format
• x and y: map positions of the current Monitor Unit for the Event Viewer feature

Back

Inserting a new Monitor Unit

A new Monitor Unit can be inserted into the database by this command:

insert into t_monitor_data (monitor_id, description, ip, x, y) values
    ('MNi', 'description', 12345345, 100, 200);

Be aware that monitor_id must be unique in the whole system. The IP address should be in special format generated by ServMisc utility.

Back

Deleting a Monitor Unit

The following command deletes a Monitor Unit from the database:

delete from t_monitor_data where monitor_id = 'MNi';

MNi should be replaced by the ID of Monitor Unit to be deleted.

Back

Modifying a Monitor Unit

To change the data of a Monitor Unit type the following command:

update t_monitor_data set description = 'desc', ip = 1234567890 where monitor_id = 'MNi';

MNi should be replaced by the ID of the Monitor Unit. After set name of columns to be changed with the new values separated by commas should be enumerated .

Back

Listing Signaling Links

Existing Signaling Links can be listed by this command:

select * from t_sl_data;

Columns in t_sl_data table:

• sl_id: identifier of the Signaling Link
• description: long name of the Signaling Link used by the Client modules
• group_id: name of the Signaling Link group that the Sugnaling Link belongs to
• status: status of the Signaling Link
• deleted: if it is 0 the Signaling Link is existing and used, if 1 it is not
• x: map positions of the current Monitor Unit for the Event Viewer feature y: map positions of the current Monitor Unit for the Event Viewer feature
• ddf_id: DDF identifier where the signalink Link is connected to
• card_ID: identifier of the SGA card which the Link belongs to

Back

Making relation between Monitor Units and Signaling Links

The Sga-7N Monitoring System stores which Sugnaling Link belongs to which Monitor Unit in the t_sl_monitor_rel table.

To make a new relation:

insert into t_sl_monitor_rel values ('SLi', 'MNi', current_timestamp, null);

To set an association to be deleted:

update t_sl_monitor_rel set endtime = current_timestamp where sl_id = 'SLi' and monitor_id = 'MNi';

To list relations:

select * from t_sl_monitor_rel;

Columns in t_sl_monitor_rel table:

• sl_id: Signaling Link identifier
• monitor_id: Monitor Unit identifier
• starttime: the relation between the given Signalink Link and Monitor Unit is valid from this timestamp
• endtime: timestamp when from the association is deleted

Back