Missions and Parts are inserted manually into the database. Units can be inserted either manually or using the tools described in this section. There are a lot more telemetry points in comparison (up to 5000 per Part), and there are a few ways to insert them more efficiently.
5.2.1 From Spreadsheet
The telemetry points for all missions are stored in a spreadsheet the author created. The format of this spreadsheet is shown in Figure 5.2. It contains all the mandatory fields for the TelemetryPoints database table.
Figure 5.2: Telemetry Points Excel Spreadsheet Format
From that spreadsheet, SQL insertion statements can be generated with Excel formulas. The insert commands are in the form:
INSERT INTO TelemetryPoints ( PartID, PointID, Desc_Short, SourceUnits, SourceValueType ) VALUES (19,10,'time', 's', 1);
Chapter 5. Inserting data into the database 30 When all of these commands are generated in Excel, they can be saved into a SQL script and run in a PostgreSQL database tool such as pgAdmin or psql.
5.2.2 From Parsed Whole Orbit Data Files
Another way to insert points when the points are not already in the spreadsheet is by using a parsed WOD file in CSV format. The author wrote a script that takes in the CSV WOD files that WODChopper created and inserts the names in the header as telemetry points into the database. It also generates a parameter map that maps the database IDs to the column numbers in the CSV file. This script loops through the header and for each column header it checks if the name is already in the TelemetryPoints table in the database. If it does, it is added to the parameter map. If not, the script assigns the point a PointID. First, it tries to use the column number as the PointID, but if the column number already exists in the TelemetryPoints table with the current PartID, the script uses the next available PointID. Then, it inserts the telemetry point into the database and adds it to the parameter map.
5.2.3 From Whole Orbit Data Chopper Configuration File
The most consistent way to insert telemetry points is to use a WODChopper configuration file. This is because different missions often share the same tags for the same telemetry points in the configuration file.
The author developed a script to add database IDs to the WODChopper configuration file and insert new telemetry points at the same time. Figure 5.3 shows the process of this script that takes in a WODChopper configuration file and outputs a new one with database IDs. The prerequisites to running the script are:
All tables in the database are created based on the schema shown in Figure 3.1.
The Satellites and Parts tables are populated.
The SourceValueTypes table is populated.
To use the script for a new mission, one needs to modify three inputs to the script. The first is the database configuration file "config.txt" which contains the connection information for the database. The second is a
"split_names" list in the code which contains the names of WOD groups that include telemetry points from multiple Parts. For example, "Power System High Rate" is a WOD group that includes telemetry points from the battery module, multiple switches, and the solar array monitor. For each point in this group, the Tag in the WODChopper configuration file is prefixed with the Part or Unit name, such as "SPN1_Current1", where "SPN1" is the Unit name and "SPN" is the Part name. So "Power System High Rate" should be included in "split_names". On the other hand, the "HKC" WOD group only contains points for a single Part: House Keeping Computer (HKC), so it should not be in "split_names". In this case, "HKC" is both the Part name and the Unit name. Lastly, a map from Unit names to Part names needs to be provided to the script. This can be implemented as an if-else block. For example, all Units with names that contain
"SPN" (ex. "SPN1", "SPN2", ...) should have "SPN" as the Part name.
Chapter 5. Inserting data into the database 31
Figure 5.3: Flowchart of the program that inserts telemetry points into the database and adds IDs to WOD-Chopper Config file
With these inputs, the script first gets all the existing Units, Parts, telemetry points, and telemetry types information from the database and stores them in the "database dictionary". Then, it parses the WOD configuration XML file, going through it point by point. For each point, it determines the Unit name, Part name, and point StringID based on "split_names" and the "unit_name to part_name map" mentioned above. The point StringID is the Tag without the Unit/Part prefixes. If the Part name doesn’t exist in the database, the script continues to the next point. If the Unit name doesn’t exist in the database, the script inserts the new Unit into the database. Now if the point StringID already exists in the database, the script simply adds the corresponding UnitID, PartID, and PointID to the WODChopper configuration file. It also inserts the point’s custom name into the TelemetryPointNames table. This is because the same telemetry point for the same Part can have different custom names for different Units. For example, the telemetry point "SPN1_switch2_current" can have names "SPN1 Switch2 IOUT HKC" and "SPN1 Switch2 IOUT X-Wheel" for two different missions. The custom names allow one to identify them easily when querying the database. If the point StringID does not exist in the database, the script also inserts the new telemetry point into the database. A major advantage of this script is that it can add new missions to the same database, as it has the ability to check for existing information in the database. One drawback is that the “split_names”
list and the “unit_name to part_name map" have to be defined for each mission. In future updates, the script might be able to determine these inputs automatically from existing information.
Chapter 5. Inserting data into the database 32