Components

Oracle account and client software

All the database tables are in the schema (username) DAT_GROUP in the Oracle instance SLACDEV. At the time of this writing SLACDEV is running Oracle 11g, in which usernames and passwords are case-sensitive.

DAT configuration files and software

The DAT software is mostly written in Python version 2.5. A few shared libraries are used via ctypes bindings in order to order to access I2C devices and get the layout of the board EEPROM data. The database itself is accessed via the cx_Oracle driver which comes with the RHEL5 Python 2.5 installation.

The account information needed to connect to Oracle is not embedded in the program code but is placed in several configuration files. One file named tnsnames.ora defines the service name COBDB in terms of the host name, port number and instance. Another named sqlnet.ora gives the location of a directory containing an Oracle "wallet" which relates service names to usernames and passwords.

DAT repository

The DAT source code for the database resides in the SVN repository /afs/slac/g/cci/repositories/ctkrepo under the directory IPMC/.

• LinkM USB-I2C dongle driver.
  • thirdParty/linkm/
• I2C device library and its Python wrapper.
  • hal/i2c/
• COB library and database application.
  • hal/cob/

AFS installation

A self-contained installation of the database application can be found at /afs/slac/g/cci/cobdb. The directory tree looks like this:

• bin/
  • interpreter
• lib/
  • libi2c.so
  • liblinkm.so
• oracle/
  • admin/
    • tnsnames.ora
    • sqlnet.ora
  • wallet/
• py/
  • i2clib.py
  • pycob/
    • __init__.py
    • cobxml.py
    • conversions.py
    • database.py
    • devices.py
    • interpreter.py
    • structures.py
    • tests.py

Follow these steps to create such an installation at inst:

1. Create an empty installation tree under inst.
2. Check out a working version of the IPMC project under work.
3. Build optimized versions of work/thirdParty and work/hal for x86_64-linux.
4. Copy work/build/thirdParty/lib/x86_64-opt/liblinkm.so and work/build/hal/lib/x86_64-opt/libi2c.so to inst/lib/.
5. Copy files from work/hal/i2c/src/linux/
   1. i2clib.py and pycob/ to inst/py.
   2. interpreter to inst/bin/.

Normally you won't need to create inst/oracle/ and its contents, just use the one in the standard AFS installation; access to it is set up by the standard DAT setup scripts. Just in case you need to do it, though:

1. Copy from work/hal/i2c/src/linux/
   1. tnsnames.ora and sqlnet.ora to inst/oracle/admin/. You'll need to edit the copy of sqlnet.ora if you're going to use your own wallet.
2. Create inst/oracle/wallet.
3. Use mkstore to create the Oracle wallet.
   1. mkstore -wrl inst/oracle/wallet -create
   2. mkstore -wrl inst/oracle/wallet -createCredential cobdb DAT_GROUP password-for-DAT-group

Each time it runs mkstore will ask you for the password of the wallet which is not the same as the password for user DAT_GROUP. Database code doesn't need to know the wallet password.

Database code development

I strongly recommend that you avoid testing new database code against the production Oracle database for DAT_GROUP on SLACDEV. Instead, run the interpreter using one of the two following commands:

_inst_/bin/interpreter --dbtype=sqlite --connect=:memory:
_inst_/bin/interpreter --dbtype=sqlite --connect=DATABASE-FILENAME

SQLite is a low-overhead relational database that doesn't require a server; it can create a database in memory or in an ordinary file.

If you really need to develop against an Oracle database you can install Oracle Express Edition on one or more machines. In this case you'll have to set up the server networking, accounts, etc. but it isn't all that bad.

Backup policy

All data on SLACDEV is backed up daily.

User manual

Running the interpreter

Once you've sourced one of the standard DAT setup scripts, the following command will run the database front-end:

/afs/slac/g/cci/cobdb/bin/interpreter
/afs/slac/g/cci/cobdb/bin/interpreter --batch=COMMAND-FILE-NAME

Interactive mode

In interactive mode each command is by default a separate database transaction; each change you specify will immediately become visible to other users. You can start a larger transaction with the begin command and end it with either commit or rollback. In the latter case the database will revert to its state of just before the begin. Sorry, any changes you made to board EEPROMs are not magically erased! After a commit or a rollback you once again enter one-transaction-per-command mode until the next begin. A command error inside a transaction will act just like rollback.

Batch mode

In batch mode the entire command file is treated as if it started with begin and ended with commit. If the resulting transaction is too large or takes too long you can split it up using commit commands; each commit is treated as if it were followed immediately by a begin so batch execution never enters one-transaction-per-command mode. Command errors cause a rollback of the current transaction and also cause the interpreter to exit. Note that having the entire batch execution be a single transaction means that the run has no effect on the database unless all commands run without error.

Destroying and rebuilding the database

Don't do this unless the production database has gotten so completely messed up that you need to rebuild it from scratch, e.g., using batch mode to run a file written by the dump db command. Even then it's better to send e-mail to db-admin@slac.stanford.edu asking them to restore the DAT_GROUP schema tables BOARD, MAC, LOG, REPLACE_ON_BURN and PARAMETER from the latest SLACDEV backup.

sqlplus /@cobdb
drop table board cascade constraints;
drop table mac;
drop table log;
drop table replace_on_burn;
drop table parameter;
exit;
/afs/slac/g/cci/cobdb/bin/interpreter --batch=COMMAND-FILE-NAME

Commands

add

The 'add' command is used to make new log entries, board records and
MAC address records, or to restore a database record from a dump.

New log entry

add log entry [board BOARD-REF] [at TIMESTAMP] comment REST-OF-LINE

Most log entries will refer to a specific board but general entries
can also be made. Normally you won't specify the UTC timestamp
yourself in which case the current date and time is used. The part of
the line after 'comment' will be stipped of leading and trailing
whitespace before being stored. The following example will add an
entry for the last board mentioned in previous commands:

add log entry board * comment Repaired.

New board

add (rtm | ftm | cmb1 | cmb2) board HEXNUM TYPE [(good | bad)] [retired]

The command 'show board type' will print the type names allowed for
each supertype ('rtm', etc.). Boards are identified with unique hex
serial numbers. If you omit the good/bad flag the board is assumed
to be good. If you omit 'retired' the board is assumed to be still
in service. Examples:

add rtm board fbacc3 snap-12 good retired
add cmb1 board 1023005 dtm405

New MAC address

add mac MAC-ADDRESS board BOARD-REF used at TIMESTAMP [retired at TIMESTAMP]

The database has a record for each MAC address allocated to a device
from the block of addresses assigned to the DAT project. Unallocated
addresses don't have records. The first timestamp shows when the address
was last allocated. When the associated board is retired the second
timestamp is added.

Normally you don't add MAC records by hand. Instead they're created
by the 'init eeprom' command.

begin

Starts a multi-command database transaction unless one is already
active, in which case the command is ignored. In batch mode a
multi-command transaction is always in effect so this command is
useless (but harmless). In interactive sessions each command is in its
own transaction until 'begin' is used.

After a multi-command transaction is terminated the behavior of the
interpreter differs between interactive and batch modes. In
interactive sessions the interpreter reverts to its default behavior
of placing each command in its own transaction. In batch mode a new
multi-command transaction is started.

If a multi-command transaction is in effect and you use 'exit' or a
similar command, you'll be asked if you want to commit the transaction
before the interpreter exits.

SOME COMMANDS PUT A LOCK ON PART OR ALL OF THE DATABASE. Such locks
are maintained, potentially making other database users wait, until
the current transaction terminates. For example, 'init eeprom' puts a
lock on MAC address allocation when used for network-capable
boards. Until that lock is released other users trying to allocate MAC
addresses will have to wait. Try to keep such commands out of
long-lived transactions.

See also the help text for 'commit' and 'rollback'.

begin [transaction]

commit

If a multi-command transaction is in effect then this command
terminates the transaction and commits all the changes made in it to
the database; otherwise the command is ignored. See also the
help text for 'begin'.

dump

One form of the 'dump' command is used to write database records in a
text form easily parsable by a computer program. The other reads and
dumps the contents of the device EEPROM connected on a USB port using
the LinkM dongle and a test fixture.

Dump database as Confluence wiki markup

dump db [to '"'FILENAME'"'] as wiki

Dump database as interpreter commands

For eyeballing or later use in batch mode. Omitting the 'to' clause
will send the dump to stdout. Omitting the record type will cause
the entire database to be dumped.

dump db [to '"'FILENAME'"'] [(parameter | board | mac | log)]

EEPROM dump

Omitting both 'hex' and 'xml' will cause both formats to be
used while using just one of them will suppress the other
format. The XML dumps can be quite large and repetitive so there's
an option to limit the number of lines written.

dump eeprom [to '"'FILENAME'"'] [hex] [xml [lines DECNUM]]

erase

This command will erase a board EEPROM connected to the LinkM USB
dongle. Do this only if you need to do 'init eeprom' on a board which
isn't in the database but whose EEPROM has already been written to for
testing purposes.

erase eeprom

exit

Various commands will end a session as will end-of-input (simulated
interactively with ctrl-D). If a multi-command transaction is in
effect you'll be asked if you want to commit it before exiting the
interpreter. See the help text for 'begin'.

exit | stop | quit | halt | bye | end | END-OF-INPUT

init

This command will read an XML file and use it to set the content of a
blank EEPROM connected to the LinkM USB dongle. Some of the XML
elements will have their values replaced before the EEPROM is written,
e.g., MAC addresses. 'show replacement' will get you the whole list.
MAC addresses are assigned and records for them generated automatically.
A log entry is made using the comment you provide.

Some tests of board functions will also be made, with your
cooperation. For instance, RTM boards will have their various LEDs
lit and you'll be asked some yes or no questions about them.

This may command may temporarily lock other database users out of MAC
address allocation. See the help text for 'begin'.

This command won't re-init a board that's already in the database. If
you need to change EEPROM parameters try using 'restore eeprom'.

init eeprom from '"'FILENAME'"' comment REST-OF-LINE

restore

This command restores the contents of the EEPROM using a complete XML
file, e.g., one produced by 'dump eeprom' with only the 'xml' option
and no 'hex' or 'lines' options. Only a board already in the database
can have its EEPROM restored and you may not change its supertype or
its MAC addresses. Use this command when you need to tweak other
EEPROM parameters for a board already registered.

restore eeprom from '"'FILENAME'"' comment REST-OF-LINE

rollback

If a multi-command transaction is in effect then this command
terminates the transaction and rolls it back, i.e., none of the changes
specified in the transaction actually make it to the database.
Outside of multi-command transactions this command is ignored.
See also 'begin' and 'commit'.

show

'show' produces a more readable output for database records than
'dump' and for a couple of other types of items (not EEPROM,
though). The log display allows the use of a regular expression to
match the comment portions of the desired entries.

show parameter [PARAM-NAME]
show replacement
show board type
show board with mac MAC-ADDRESS
show board BOARD-REF
show mac MAC-ADDRESS
show log [backwards] [from TIMESTAMP] [to TIMESTAMP] [containing REGEX]

PARAM-NAME is one of 'db schema', 'eeprom schema' or 'next mac'. The schema
numbers recorded in the database are the version numbers assigned to
the layout of database tables and the layout of data in the device
EEPROMS. MAC addresses are assigned sequentially where the value of
'next mac' is the one that will be assigned by the next 'init eeprom'
command.

'show replacement' will list those XML elements whose values are
replaced by the 'init eeprom' command.

Log entries are shown in chronological order unless 'backwards' is
specified. Omitting a timestamp will include all entries either from
the first or up to the last entry. The time interval test is
inclusive.

The rest of the line after 'containing' is stripped of leading and
trailing whitespace and interpreted as a Python regular expression
(you can escape significant leading and trailing spaces with backslashes).
The comment portion of each log entry in the given time interval is
searched for a match and the entry is printed only if a match is
found. The matching is case sensitive unless you begin your
regular expression with '(?i)'. Examples:

show log backwards from 2012-06-01 00:00:00 containing (?i)broken|busted
show log containing \bLED(s|S)?\b

update

The 'update' command is used to change information in existing
records.

update (board BOARD-REF | mac MAC-ADDRESS) SIMILAR-TO-ADD comment REST-OF-LINE

Save for the comment, what follows the board ref or MAC address is
just like what's used in the corresponding 'add' command except that
all items are optional; you need only specify the items that need to
be changed. You can't change the supertype of a board or the address
part of a MAC record. Examples:

add cmb1 board c30f dpm440 good
update cmb1 board * dpm405 comment Got the CPU wrong.
update cmb1 board * bad retired comment Exploded!

That last update will also update the retired-at timestamps in any MAC
records associated with the board. Updating MAC records directly
should be left to database experts.

Terms appearing in command descriptions, miscellany.

batch mode

Commands may be run in batch mode using the '--batch' option on the
shell command line when invoking the interpreter. Database changes are
not saved until the session ends without error. Any error ends the
session and revokes any pending changes. See 'begin'.

board-ref

A board is identified by its built-in serial number which is always
specified as a hex number with no leading '0x' or '0X'. You may use
both upper and lower case letters. Each time you use a hex board number
a 'last board referenced' variable is set to it. You can then refer to the
same board again using an asterisk.

add log entry board f0c5 comment Wonky LEDs.
add log entry board *    comment Unreliable EEPROM readout.

comments

Not to be confused with log entry comment text, comment lines have '#'
as their first non-whitespace character and are ignored by the
interpreter. A comment can't be on the same line as a command except
for the empty command.

filename

A filename is always specified inside double quotes in order to
prevent parsing problems. Filenames may therefore contain any
characters except double quotes. In particular, whitespace is
preserved.

mac-address

MAC addresses are hex numbers in upper or lower case (or mixed) with
no leading '0x' or '0X'. All valid addresses fall between 080056004000
and 080056007fff inclusive.

spaces and blank lines

Whitespace is used a a separator and a run of many spaces is generally
equivalent to one space except in filenames. Each command line is
stripped of leading and trailing spaces before execution. Empty lines
are ignored.

timestamp

A timestamp is specified as two groups of decimal numbers separated by
whitespace in the form YYYY-MM-DD HH:MM:SS, e.g.,
2012-05-30 23:40:55. It is interpreted as UTC date and time.

Command grammar

Here's the command grammar in Extended Backus-Naur Form, or EBNF. Each rule begins with the name of an entity followed by "=" and then the definition terminated by a period. Words in quotes appear literally, i.e., they are not the names of rules to be substituted. Items are assumed to be separated by whitespace, e.g.,
"foobar" is never interpreted as "foo" followed by "bar"; that would have to be written as "foo bar". A run of whitespace is treated as if it were a single space. "|" is used to indicate alternatives. Parentheses are used for grouping. "[" and "]" enclose optional items. "{" and "}" enclose item sequences that may be repeated zero or more times (at least once if a plus sign follows the trailing brace).

program = {command END-OF-LINE}.

command = addCommand      | updateCommand | setCommand   | initCommand   |
          eraseCommand    | dumpCommand   | showCommand  | 
          exitCommand     | helpCommand   | beginCommand | commitCommand |
          rollbackCommand | restoreCommand.



addCommand = "add" (macAdd | logAdd | boardAdd).

macAdd = "mac" macAddress "board" boardRef ["used" "at" timestamp] ["retired" "at" timestamp].

logAdd = "log" "entry" ["board" boardRef] ["at" timestamp] "comment" REST-OF-LINE.

boardAdd = (cmb1 | "rtm")   "board" HEX TYPENAME ["good" | "bad"] ["retired"].



updateCommand = "update" (macUpdate | boardUpdate).

macUpdate = macAdd "comment" REST-OF-LINE.

boardUpdate = ("cmb1" | "rtm")   "board" boardRef [TYPENAME] ["good" | "bad"] ["retired"] "comment" REST-OF-LINE.



setCommand = "set" paramInfo.

paramInfo = dbSchemaParam DECIMAL | eepromSchemaParam DECIMAL | nextMacParam macAddress.

paramName = dbSchemaParam | eepromSchemaParam | nextMacParam.

dbSchemaParam = "db" "schema".

eepromSchemaParam = "eeprom" "schema".

nextMacParam = "next" "mac".



dumpCommand = "dump" (dbDump | eepromDump).

dbDump = "db"  ["to" filename] ["as" "wiki" | dbSelection].

dbSelection = "parameter" | "board" | "mac" | "log".



initCommand = "init" "eeprom" initInfo.

restoreCommand = "restore" "eeprom" initInfo.

eraseCommand = "erase" "eeprom".

eepromDump = "eeprom"  ["to" filename] { "hex" | "xml" ["lines" DECIMAL] }.

initInfo = "from" filename "comment" REST-OF-LINE.



showCommand = "show" (paramShow | boardShow | macShow | logShow | replacementShow ).

paramShow = "parameter" [paramName].

boardShow = "board" ("type" | "with" "mac" macAddr | boardRef).

macShow =  "mac" [macAddress].

logShow =  "log" ["backwards"] ["from" timestamp] ["to" timestamp] ["containing" REST-OF-LINE].

replacementShow = "replacement".



exitCommand = "exit" | "stop" | "quit" | "halt" | "bye" | "end" | EOF.



beginCommand = "begin" ["transaction"].



commitCommand = "commit".



rollbackCommand = "rollback".



filename = DOUBLE-QUOTE {anything-but-double-quote}+ DOUBLE-QUOTE.

macAddress = HEX.

boardRef = HEX | "*".

timestamp   = DECIMAL "-" DECIMAL "-" DECIMAL DECIMAL ":" DECIMAL ":" DECIMAL