Play SQL Spreadsheets 2.10 was a major release.
Back-up before upgrading
There are several upgrade tasks in this version. It means the schemas are modified. If you want to be on the safe side, you can perform a back-up of your database before upgrading the plugin. However, with Postgres' transactional DDL, the full upgrade will be reverted if an error is encountered.
- The upgrade is performed when the plugin first accesses a schema in read-write mode. It is not performed when the plugin itself is upgraded.
- The version is saved in the system table named PLAYSQL_SETTINGS.
Schema changes
- New system tables:
- PLAYSQL_SETTINGS - For various settings
- PLAYSQL_AUDIT_TRAIL - For the audit trail (history)
- PLAYSQL_FORMULA_DEPENDENCIES - For links between formulas.
- PLAYSQL_ENTITIES - For Queries and Joint Tables.
- The POSITION column
- Play SQL creates tables with an ID and POSITION column.
- POSITION used to be an integer,
- We've transform POSITION to contain TEXT/JSON.
- We write the row's metadata in the POSITION column.
- If the metadata can't be parsed in JSON, it is considered null.
- For example, if a cell has a formula, the formula won't be saved in the cell itself but in the POSITION column, as such:
{ "c_mycolumn" : { "formula" : "=1+2" } }
- The PLAYSQL_FORMULA_DEPENDENCIES contains the up-to-date index of the formulas;
- It can be reindexed by hitting the right REST endpoint,
- It contains the origin column(s) and row(s), the destination columns/rows and the formula,
- If there is a discrepancy between this formula and the formula in the row metadata of the table, then the formula of the PLAYSQL_FORMULA_DEPENDENCIES table will be executed until it is reindexed.
Where queries are saved
We've changed the place where queries are saved:
- For read-only schemas, they are saved in the Confluence space,
- For read-write schemas, they are saved in the Postgres schema, in the table PLAYSQL_ENTITIES.
- Queries have an ID. All IDs under 1000 are stored in the Confluence space. All IDs above 1000 are stored in the Postgres schema.
- Queries which were created in the Confluence space aren't moved to the Postgres schema. This is the case for queries created when the database is read-only and queries created before Play SQL 2.10.
It may impact users and query visibility.
- If you plug a new space to the same datasource with the same schema, the same queries will be visible.
Reverting
If you want to reopen a schema with an older version of Play SQL (<2.10):
- Transform all POSITION columns to integer,
- Remove the PLAYSQL_FORMULA_DEPENDENCIES and PLAYSQL_ENTITIES,
- Revert the value in PLAYSQL_SETTINGS to L1_AUDIT_TRAIL.