Introduction to BS2SQL
BS2SQL, short for BizzStream to SQL, is a module designed to store the content of BizzStream documents in an SQL database, enabling seamless integration with advanced data-handling systems and business intelligence (BI) platforms. Currently, PostgreSQL is the only supported database. To use this module, you must have access to a PostgreSQL database with permissions to create and modify tables, as well as insert data. Additionally, the module must be enabled in your environment. If it is not already active, please 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 follows a First-In, First-Out (FIFO) order to ensure sequential data processing. If an error occurs during processing, the affected items remain in the queue until they can be successfully written to the SQL database. This ensures that no updates are lost, but it 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. This means that any subsequent updates to these documents will not be processed or stored in the SQL database. However, documents that were already in the SQL database will remain there; they will not be removed. Additionally, any documents that were already in the queue before the setting was disabled will still be processed.
Actual documents
When activated, the BS2SQL module automatically stores the current version of each BizzStream document in the SQL database. For every document definition exposed as SQL, the module generates a table to manage its header fields. For any 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 or 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 Bizz 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 BizzStream 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 the standard fields, the tables also include columns corresponding to the fields specified in the document definition.
In tables created for lines, the structure is based on that of the header tables, including the same standard fields such as document_id and created_by_user_id. Additionally, these tables have two extra 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. |
Re-enabling
If the “Expose to SQL” setting is re-enabled, all documents based on that definition will be placed in the queue and processed again. As a result, documents in the actual tables will be overwritten with the latest versions from the queue.