Difference between revisions of "Stardust/Knowledge Base/Infrastructure System Administration Maintenance/SQL"

Revision as of 01:28, 21 October 2011

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 );