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.
Difference between revisions of "Stardust/Knowledge Base/Infrastructure System Administration Maintenance/SQL"
m |
(→Process History) |
||
Line 56: | Line 56: | ||
== Process History == | == Process History == | ||
− | SQL statements | + | Following SQL statements (Oracle specific date formatting used) allow to retrieve activity instance information that belongs to a particular process instance and its hierarchy (root process with all subprocesses). |
The first statement can be used if the access point is the root process instance OID. | The first statement can be used if the access point is the root process instance OID. |
Revision as of 06:53, 31 October 2011
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 );
Process History
Following SQL statements (Oracle specific date formatting used) allow to retrieve activity instance information that belongs to a particular process instance and its hierarchy (root process with all subprocesses).
The first statement can be used if the access point is the root process instance OID.
SELECT ai.oid, ai.processinstance, pd.name as "PROCESS", ad.name as "ACTIVITY", to_char(to_date('01-JAN-1970','DD-MM-YYYY') + ( ai.starttime / (1000 * 60 * 60 * 24) ), 'YYYY-MM-DD HH24:MI:SS') "STARTTIME", to_char(to_date('01-JAN-1970','DD-MM-YYYY') + ( ai.lastmodificationtime / (1000 * 60 * 60 * 24) ), 'YYYY-MM-DD HH24:MI:SS') "LAST MODIFICATION", (ai.lastmodificationtime - ai.starttime) "DURATION (ms)" FROM activity_instance ai INNER JOIN procinst_scope pis on (ai.processinstance = pis.processinstance) INNER JOIN activity ad on (ai.activity = ad.oid and ai.model = ad.model) INNER JOIN process_definition pd on (ad.processdefinition = pd.oid and ad.model = pd.model) WHERE pis.rootprocessinstance = ? ORDER BY ai.starttime asc, ai.oid asc;
The second statement can be used if the access point is somewhere in the mid of the process hierarchy.
SELECT ai.oid, ai.processinstance, pd.name as "PROCESS", ad.name as "ACTIVITY", to_char(to_date('01-JAN-1970','DD-MM-YYYY') + ( ai.starttime / (1000 * 60 * 60 * 24) ), 'YYYY-MM-DD HH24:MI:SS') "STARTTIME", to_char(to_date('01-JAN-1970','DD-MM-YYYY') + ( ai.lastmodificationtime / (1000 * 60 * 60 * 24) ), 'YYYY-MM-DD HH24:MI:SS') "LAST MODIFICATION", (ai.lastmodificationtime - ai.starttime) "DURATION (ms)" FROM activity_instance ai INNER JOIN procinst_hierarchy pih on (ai.processinstance = pih.subprocessinstance) INNER JOIN activity ad on (ai.activity = ad.oid and ai.model = ad.model) INNER JOIN process_definition pd on (ad.processdefinition = pd.oid and ad.model = pd.model) WHERE pih.processinstance = ? ORDER BY ai.starttime asc, ai.oid asc;
Both queries return a process history, showing all activity instances with start and complete times as well as duration in ms.