HAS-Motion Software Documentation

User Tools

Site Tools

You are here: Main Page » visual3d » documentation » definitions » file_formats » SQL Database Definition
visual3d:documentation:definitions:file_formats:sql_database_definition

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
visual3d:documentation:definitions:file_formats:sql_database_definition [2025/07/17 19:36] – sgrangervisual3d:documentation:definitions:file_formats:sql_database_definition [2025/07/17 19:39] (current) – sgranger
Line 22: Line 22:
  
 **Sample SQL scripts** to create this table structure are available for SQL Server, MySQL, and MS Access. **Sample SQL scripts** to create this table structure are available for SQL Server, MySQL, and MS Access.
- 
-\\ 
- 
  
 ===== Parsing Raw Data ===== ===== Parsing Raw Data =====
Line 30: Line 27:
 The data exported from Visual3D into a database is too complex and dynamic for a rapid database transfer. The overhead associated with SQL Insert commands is quite high, even with optimization. For that reason, the data is formatted into a delimited structure to make intelligent post-process parsing possible. For each signal, a database row is created consisting of the signal identifier (i.e. foreign key) and a big string of raw data. The raw data - whether target data per frame, analog data, or calculated metrics – is retrieved and parsed into the following structure. The data exported from Visual3D into a database is too complex and dynamic for a rapid database transfer. The overhead associated with SQL Insert commands is quite high, even with optimization. For that reason, the data is formatted into a delimited structure to make intelligent post-process parsing possible. For each signal, a database row is created consisting of the signal identifier (i.e. foreign key) and a big string of raw data. The raw data - whether target data per frame, analog data, or calculated metrics – is retrieved and parsed into the following structure.
  
-\\+<code>
 ^ frame_number @ label 1 @ data_value 1 @label 2 @ data_value 2 …. @ label x @ data_value x ^ frame_number @ label 1 @ data_value 1 @label 2 @ data_value 2 …. @ label x @ data_value x
- +</code>
-\\ +
  
 ===== Signal Example ===== ===== Signal Example =====
  
-For a target signal called “LANK” we would see a row in the database for the signal name in L4_name table with a system generated ID called dnameID and a dataname. For example: 849 “LANK” +For a target signal called “LANK” we would see a row in the database for the signal name in L4_name table with a system generated ID called dnameID and a dataname. For example: 849 “LANK” \\ 
- +\\ 
-\\+
 In the L6_framedata table, we would then see the raw data associated with “LANK” represented as a row containing a system generated ID, the foreign key linking back to the L4_name table, and the data string. For example, for signal 849 (i.e. “LANK”), we would see 10 frames of data like this: In the L6_framedata table, we would then see the raw data associated with “LANK” represented as a row containing a system generated ID, the foreign key linking back to the L4_name table, and the data string. For example, for signal 849 (i.e. “LANK”), we would see 10 frames of data like this:
  
Line 57: Line 51:
  
 This approach makes parsing the data fairly straightforward using the built-in parsing tools of today’s programming languages. For example, In Visual Basic, using the SPLIT command and Direct Access Object (DAO) methods for managing databases, the code to parse and store the data into the L5_framedata table would look like: This approach makes parsing the data fairly straightforward using the built-in parsing tools of today’s programming languages. For example, In Visual Basic, using the SPLIT command and Direct Access Object (DAO) methods for managing databases, the code to parse and store the data into the L5_framedata table would look like:
- 
-\\ 
- 
  
 <code> <code>
Line 99: Line 90:
 </code> </code>
  
-\\ +**Note** this routine could potentially take a very long time to run since a SQL INSERT is performed for every component of every frame of data. \\ 
-Note that this routine could potentially take a very long time to run since a SQL INSERT is performed for every component of every frame of data. +However, once parsed and inserted into the L5_framedata table, it is very easy and fast to access the data for analysis. This is where pivot tables play an important role. They permit the dynamic storage of data components to be managed since it is impossible to accommodate a data structure for every potential digital and analog motion capture related signal generator. \\
- +
-\\ +
-However, once parsed and inserted into the L5_framedata table, it is very easy and fast to access the data for analysis. This is where pivot tables play an important role. They permit the dynamic storage of data components to be managed since it is impossible to accommodate a data structure for every potential digital and analog motion capture related signal generator. +
- +
-\\+
 However, pivot tables can be avoided if only certain information, in a consistant format is needed, then new tables can be created to improve the speed of the parsing process. However, pivot tables can be avoided if only certain information, in a consistant format is needed, then new tables can be created to improve the speed of the parsing process.
  
  
  
visual3d/documentation/definitions/file_formats/sql_database_definition.txt · Last modified: 2025/07/17 19:39 by sgranger