SQL Cheatsheet for AIDA development

This page just lists notes for use of SQL aida development. See also SQL syntax Cheat sheet for SQL commands,  AIDA Directory Database documentation, ; Aida Directory Service Database Schema Diagram.

SQL Login

First "login" to sql using your favourite interface (sqlplus, or sql-oracle mode from emacs). This must be on a SLAC afs node. The development AIDA directory database has username AIDADEV, the production AIDA directory database has username AIDAPROD. Both are in the SLACPROD instance.

AIDA Directory Database and Namespace Querying

This section describes how to examine the contents of the Aida database

Show the data providers servicing the attributes of a given instance

SELECT aida_names.instance, aida_names.attribute, aida_directory.service_id
FROM AIDA_NAMES, aida_directory
WHERE aida_names.instance = 'HB60:IPVAL:X' AND 
aida_directory.name_id = aida_names.id
Show all instances with attributes acquired by a given id, in this case 6 (EPICS)
AIDA_directory.name_id = aida_names.id

Aida Database Maintenance and Update

This section describes how to update items in the Aida directory.

To add an instance//attribute pair to the Aida Directory Service

In the following example, EPICS channel pr00:asts:ch117.val is added to the Aida Directory Service. Note, you have to know at least the instance and attribute name you want to use, and the SERVICE_ID of the Data Provider which shall be responsible for the instance//attribute (6 in the example below). Additionally you can specify a transform string (not shown in this example).


1 row created.

SQL> select id from aida_names where instance = 'PR00:ASTS:CH117';



1 row created.


The above can be competed with 2 simple scripts, one to find the SERVICE_IDs of all the existing services, and another to add an instance//attribute and assign it to a service (in one go). This example adds P2BPMLER//BPMS and says that it'll be managed by the SLCBpm data provider.

SQL>  @/afs/slac/g/cd/soft/ref/package/aida/common/script/show_services

---------- -------------------------------------------------- ------------------
	-1 Name Server					      Name Server (Does
	 0 daServer					      Internal
	 8 SLC						      SLC Database
	 6 EPICS					      EPICS Channel Acce
	 4 SLCHist					      SLC History
	 103 SLCBpm					      SLC BPM orbit acqu

SQL>  @/afs/slac/g/cd/soft/ref/package/aida/common/script/add_IA 103 'P2BPMLER' 'BPMS'
(outout not shown)

Adding a new Data Provider

On occasion, a new Data Provider, also known as Aida "Service", must be added to the Aida directory. For instance, when we created the service which is able to read Beam Position Data from the SLC Control system (SLCBpm), we had to add the service to the Aida directory before we could add instances to the directory (see example above). All of the necessary Aida directory maintenance, such as creating a new SERVICE_ID for the service, and adding a new service interface description, is done automagically by Oracle triggers. All you have to do is insert a name and description into AIDA_SERVICES (do not specify the SERVICE_ID by hand). Note though, the first argumemt is the KEY an executing CORBA server that is implementing a service uses to register itself with aida. So that must match exactly the "aidaObjectName" argument of the _PersistentDaObject.StoreObj call in the data provider's service executable. Once you know the exact name you can use this script to add the service:

SQL> @/afs/slac/g/cd/soft/ref/package/aida/common/script/add_service 'SLCBpm' 'SLC BPM orbit acquisition' 

You can check that the new service addition worked with @show_services, as shown above.

SQL Table Management General

Typical Basic SQL statements syntax examples

INSERT INTO <table-name> (<column-name-A>,<column-name-B>...) VALUES (<value-for-A>, <value-for-B>)
DELETE FROM <table-name> WHERE <conditional-expression>
UPDATE <table-name> SET column-name = expr [,column-name = expr] WHERE <conditional-expression>

Source Database Schema Queries

This section shows how to get information from the Oracle database schema from which the Aida Directory Service is dervived. For instance, the SLC database and EPICS database databases are described as Oracle tables in other schema, outside the Aida Directory Service, but are used to create the Aida Directory Service tables.

SLC Data Schema info

To find names of SLC devices: All devices are an instance in the table NLCDA.linkable_objects.
SQL> descr nlcda.linkable_objects
 Name                            Null?    Type
 ------------------------------- -------- ----
 OBJ_AS_SET_ID                            NUMBER
 OBJ_TYPE                        NOT NULL VARCHAR2(24)
 CREATED_BY                      NOT NULL VARCHAR2(30)
 CREATED_DATE                    NOT NULL DATE
 MODIFIED_BY                     NOT NULL VARCHAR2(30)
 MODIFIED_DATE                   NOT NULL DATE
 OBJ_ID                          NOT NULL NUMBER(10)
 UNIT                                     VARCHAR2(16)
 QUALIFIER                                VARCHAR2(4)
 AREA                            NOT NULL VARCHAR2(12)
 MOD_COMMENT                              VARCHAR2(255
 SLCDB_UNIT_TEMP                          VARCHAR2(16)
 SLCDB_NAME                               VARCHAR2(20)
 OBJ_STATUS                      NOT NULL VARCHAR2(1)
The relevant columns for SLC device names are obj_type (the primary) area (the micro name, prepended by ?SLC_?), and slcdb_unit_temp (the unit number). To find valid names:SQL> select area, obj_type, slcdb_unit_temp from nlcda.linkable_objects where area='SLC_LI03' and obj_type = 'QUAD' and rownum < 6;
AREA         OBJ_TYPE                 SLCDB_UNIT_TEMP
------------ ------------------------ ----------------
SLC_LI03     QUAD                     121
SLC_LI03     QUAD                     201
SLC_LI03     QUAD                     221
SLC_LI03     QUAD                     301
SLC_LI03     QUAD                     321
To show values for SLC devices from the SLC schema (not AIDA related):
SQL> select b_max from nlcda.quad_view where obj_type='QUAD' and area='SLC_LI03' and slcdb_unit_temp
= 121;

[Aida Home Page][SLAC Controls Software Group][ SLAC Home Page]

Author:  Greg White, 15-Jul-2001
Last Modified by: Greg White, 14-Aug-2006, Updated for scripts