1. General SQL

The database may be queried by anyone with appropriate access. All tables, fields, and code inside the database is well commented.
"Admin" database access is needed to execute Procedures, but "User" is enough for most other purposes.
The below samples might have a LIMIT 100 clause or be partially commented-out, and might need slight adjustments.

Diagram of the Main tables and their relationships

(Diagram by https://dbschema.com/databases/mariadb.html)

A Full Join showing all fields in the OACT process on a single row

SELECT
'ORDERS_DATA',o.*,
'AGGREGATIONS_DATA', a.*,
'CONFIRMATIONS_DATA', c.*,
'TRANSACTIONS_DATA', t.*
FROM
oact_orders o LEFT JOIN
oact_aggregations a ON o.aggregation_id=a.id LEFT JOIN 
oact_confirmations c ON a.id=c.agg_id LEFT JOIN
oact_transactions t ON 
	t.order_id=o.id AND
	t.aggregation_id=a.id AND
	t.confirmation_id=c.id
LIMIT 100

Orders are progressed through the workflow, all the way to exported transactions, via a series of Triggers and Events. Some relational rules can be validated by viewing the report_pipeline_integrity_breaches, expecting 0 results as correct state.

Extract Orders from the Database, in Import CSV format

SET @delim = ';';
SELECT
CONCAT_WS(@delim,	'item', 'external_id', 'measurement', 'amount', 'aggregatable_type', 'channel', 'order_grouping', 'order_comment')
UNION
SELECT 
CONCAT_WS(@delim,	item, external_id, measurement, amount, aggregatable_type, channel, order_grouping, NULL)
FROM oact_orders
LIMIT 100;

Orders are typically Imported, not Exported, but the above query can help with creating test-data or other technical activities.

Deadlock

• Deadlocks are a technical risk in all relational databases, and describe a situation that occurs when multiple users try to update the same data, simultaneously. The database cannot decide which edit is the most recent. Several steps have been taken actively to mitigate the risk and effects of Deadlocks in OACT, but they might still occur, for example a power outage in the middle of an operation.
• If a Deadlock happens during an export,
  • Check the database for new tables called "DATA_%_EXPORT_PROCESS" - back these up immediately if such tables exist
  • Confirm if files have been exported: if so, the IDs listed in the above tables can safely be deleted from oact_orders, oact_aggregations, oact_confirmations, and oact_transactions.
• Deadlocks are actively mitigated and/or error-handled during normal OACT processing, for example by use of temporary tables instead of core tables.
• UPDATE statements, traditionally the most common cause of Deadlocks, are almost never used in OACT - there are only 2 occassions when data is Updated, and there exist no possibilities for (normal) users to update or edit existing data.
• The most likely source of a Deadlock in OACT is while deleting data. At this point, the data is already processed and is not current.

Lost connection

• Not experienced, but technically a risk. Both the Python scripts and the PHP GUI use official MariaDB/MySQL connectors.
• In case of lost connections, the operation should be retried. Persistent and repeated errors may indicate some kind of technical issue outside of OACT.