Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Stardust/Knowledge Base/Infrastructure System Administration Maintenance/SQL
Contents
Introduction
Four all purposes but troubshooting we advise against accessing the Stardust audit trail database schema directly. Some reasons are:
- Potential side effects: The access pattern is not covered by QA and may cause unwanted side effects (locking, …)
- Separation of concerns: This is a widely accepted best practice. Accessing the db directly breaks the paradigm and creates a dependency between your code and the DB schema. The stability of the db schema is not guarantied. The API encapsulates the schema and makes potential changes transparent to the client code.
- Knowledge: You have to build up detailed knowledge of our DB schema and the support does not cover the development of SQL statements for this purpose.
- Performance: The API is optimize to access the database effectively and efficiently. It contains optimizations like e.g. the model cache that improve performance and reduce db workload. Custom direct DB operations circumvent those mechanisms.
- Effort: additional effort to re-implement existing functionality
If you feel the need to access the Stardust audit trail database schema directly then this is probably resulting from a specific requirement. Are there requirements which are not covered by the API which you are trying to fulfill with this approach? Please let the Stardust team know via the Forum. We are glad to learn about your requirements so we can suggest an approach or initiate a change or feature request that will improve Stardust.
NOTE to authors: Please make sure to specify the version on which a command was executed
[All version numbers < 7.0 refer to the Infinity Process Platform (IPP)]
Process Data
Retrieving structured data initialized with non-null or null value (plus non-leaf data in the data structure)
(Versions: 5.3.x)
SELECT pd.ID, pi.OID, SUBSTR(sd.XPATH,1,20) as "XPATH", SUBSTR(sdv.STRING_VALUE,1,20) as "STRING_VALUE", sdv.NUMBER_VALUE FROM PROCESS_INSTANCE pi, PROCESS_DEFINITION pd, DATA_VALUE dv, STRUCTURED_DATA sd, STRUCTURED_DATA_VALUE sdv WHERE pd.OID = pi.processDefinition and pi.OID = dv.processInstance and dv.data = sd.data and sd.OID = sdv.XPATH and pi.OID = sdv.processInstance;
Retrieving structured non-initialized data
(Versions: 5.3.x)
SELECT pd.ID, pi.OID, SUBSTR(sd.XPATH,1,20) as "XPATH" FROM PROCESS_INSTANCE pi, PROCESS_DEFINITION pd, DATA_VALUE dv, STRUCTURED_DATA sd WHERE pd.OID = pi.processDefinition and pi.OID = dv.processInstance and dv.data = sd.data and sd.OID not in ( select sdv2.XPATH from structured_data_value sdv2 where sdv2.processinstance = pi.OID );