VN0003 - Load Transducer Database

Instructions for loading the transducer database with data from an external system.

Question

How can I load the transducer database in VibrationVIEW with data from my existing transducer database?

Answer

VibrationVIEW uses SQLite (www.sqlite.org) as the database engine behind the transducer database. The details on how to use SQLite are beyond the scope of this document but the documentation on the SQLite website is quite good.

There are three tables that are used in VibrationVIEW, the transducer table, the settings table, and the security table.

The primary table is the transducer table. Although the fields in the table match the field in the Transducer Database dialog box in VibrationVIEW, there are some items to note.

• The GUID field is the key for the table. This should be a globally unique key. This is not visible in VibrationVIEW.
• The Axis field in VibrationVIEW is called Direction in the database
• The “Differential Input” field in VibrationVIEW is the inverse of the “Single Ended” field in the database
• If there is a 1 in the Deleted field, the transducer is considered logically deleted and will not show up in VibrationVIEW
• CalibrationDate stores the number of seconds elapsed since midnight on December 31, 1899
• Triggers are used to update the Modify Date field when a row is changed

The following fields are not currently used and should not be updated by external systems:

• Label
• Assigned System
• Assigned Channel
• Create User
• Modify User

Import

A simple way to import transducers is to use a .csv file.

1. Create a .csv file from the source database. There must be one column of source data for each column in the database. The columns can be blank.
2. Start the sqlite3 command-line program with a parameter of your database:
   • C:\bin\SQLite>sqlite3.exe
   • C:\path\to\db\xdr.db SQLite version 3.6.13.
3. Enter .help for instructions
4. Enter SQL statements terminated with ” ; ” sqlite3>
5. Set the separator to match the field separator in your .csv file sqlite> .separator
6. Import the data using the import command .sqlite> .import sourcefile.txt transducer

Synchronization

Simple synchronization between an external database and the transducer database can be done with the following steps on a periodic basis.

1. Create a .csv file from the source database
2. Delete the all transducers from the transducer database using the following commands:
   • C:\bin\SQLite>sqlite3.exe
   • C:\path\to\db\xdr.dbSQLite version 3.6.13.
3. Enter “.help” for instructions
4. Enter SQL statements terminated with a ” ; ” sqlite3>delete from transducer;
5. Import the .csv file created in step 1 using the steps described in the Import section.

Other Tables

The settings table is for internal use only and should not be updated by external tools.

The security table identifies which computers have access to update the transducer table. This is only enforced in VibrationVIEW, not in the database itself. If your interface would need to replicate this security, you would have to implement it yourself.

The key field for the Security table is “computer”. The value would be found in one of the following registry locations. The first entry takes precedents.
\HKLM\SOFTWARE\Vibration ResearchCorporation\8.0\ComputerID\HKCU\SOFTWARE\Vibration ResearchCorporation\8.0\ComputerID

If there is an entry in the table for the computer ID with the “permission” value set to 1, the computer can update the database. The user-level security is handled completely within VibrationVIEW and does not use the security table.