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.
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.
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.idShow all instances with attributes acquired by a given id, in this case 6 (EPICS)
SELECT AIDA_NAMES.INSTANCE FROM AIDA_NAMES, AIDA_DIRECTORY WHERE AIDA_DIRECTORY.SERVICE_ID = 6 AND AIDA_directory.name_id = aida_names.id
This section describes how to update items in the Aida directory.
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).
SQL> INSERT INTO AIDA_NAMES (INSTANCE,ATTRIBUTE,TRANSFORM) VALUES ('PR00:ASTS:CH117','VAL',NULL); 1 row created. SQL> select id from aida_names where instance = 'PR00:ASTS:CH117'; 1403621 SQL> INSERT INTO AIDA_DIRECTORY (NAME_ID,SERVICE_ID,"ORDER") VALUES (1403621,6,1); 1 row created. SQL> COMMIT;
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 ID NAME DESCRIPTION ---------- -------------------------------------------------- ------------------ -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)
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.
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>
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 321To 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; B_MAX --------- 51.099998
[Aida Home Page][SLAC Controls Software Group][ SLAC Home Page]
Last Modified by: Greg White, 14-Aug-2006, Updated for scripts