Introduction to BS2SQL
BS2SQL, short for BizzStream to SQL, is a module designed to store BizzStream document content in an SQL database, enabling integration with advanced data-handling systems and business intelligence (BI) platforms. PostgreSQL is currently the only supported database. To utilize this module, you must have access to a PostgreSQL database with appropriate permissions to create and modify tables, as well as insert data. The module must be enabled in your environment. If it is not active, contact BizzStream Support for assistance.
Data Publication and Processing
When a document definition is marked with "Expose to SQL," all existing documents based on that definition are placed into a queue for processing. From that point onward, every update to a document based on that document definition is also placed in the queue.
Items remain in the queue until they are successfully processed and stored in the SQL database. The queue operates on a First-In, First-Out (FIFO) basis to ensure sequential data processing. If an error occurs during processing, affected items remain in the queue until they can be successfully written to the SQL database. This mechanism prevents data loss but may result in queue growth if errors persist.
If the "Expose to SQL" setting is disabled, documents based on that definition will no longer be added to the queue. Subsequent updates to these documents will not be processed or stored in the SQL database. However, documents previously written to the SQL database will remain and will not be removed. Documents already in the queue before the setting was disabled will continue to be processed.
Actual documents
When activated, the BS2SQL module automatically stores the current version of each BizzStream document in the SQL database. For each document definition exposed to SQL, the module generates a table to manage header fields. For line blocks within the document definition, individual tables are created to handle their respective line fields.
Columns
The tables generated by BS2SQL include standard columns to ensure proper management and traceability of the document data. The following table provides a detailed description of each field:
| Column Name | Description |
|---|---|
| bs2sql_document | The entire document object stored in serialized form. |
| bs2sql_document_created_at | The timestamp indicating when the document was first inserted into the SQL table. This value remains constant even if the document is updated later. |
| bs2sql_document_updated_at | Timestamp of the most recent update of the record in the SQL table. |
| bs2sql_id | The unique identifier generated by the bs2sql module for the document record in the SQL table. |
| core_document_created_on | The creation date of the document. |
| core_document_modified_on | The last modification date of the document. |
| created_by_user_id | The user ID of the person who created the document. |
| document_definition_id | The ID of the document definition on which it was based. |
| document_id | The ID of the document. |
| last_modified_by_user_id | The user ID of the person who last modified the document. |
| status | The current status of the document (e.g., active, archived). This column is only available if the document definition has one or more statuses. |
In addition to standard fields, the tables include columns corresponding to the fields defined in the document definition.
Tables created for lines include two additional columns that establish the relationship between line records and their corresponding headers:
| Column Name | Description |
|---|---|
line_id |
A unique identifier for each line record in the table. |
header_id |
A reference to the bs2sql_id of the associated header record in the header table. |
<!-- ## History tracking The history tracking feature in BS2SQL ensures that every new version of a document is stored in the SQL database. Whenever a document is updated, a new record reflecting the latest version is created, preserving the original version for historical reference.
If a single (user) action contains multiple rules that update the document, each individual version resulting from these updates is saved as a separate record. This enables a comprehensive view of the document’s evolution, allowing users to trace changes over time and understand the sequence of updates applied to the document.
By leveraging this feature, it becomes possible to perform detailed analysis, audits, or troubleshooting by examining the document’s progression and identifying specific modifications at each step.
Columns
In history tables, separate tables are created for both header fields and line fields, mirroring the structure of their respective actual tables. These tables contain the same columns as the actual tables, with one notable exception: reference fields. For reference fields, two additional columns are included to capture both the value stored in the actual document and the historical context of the reference.
| Column name | Description |
|---|---|
actual_fieldname |
The value of the reference field as it was stored in the actual document. The part fieldname is replaced with the name of the reference field. |
history_fieldname |
If the referenced document's definition also has history tracking enabled, this field contains the bs2sql ID from the history table corresponding to the version of the document that was active when the record was inserted. The part fieldname is replaced with the name of the reference field. |