Play SQL Add-on

Space shortcuts

Page tree

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.

 

 

 

 

  • No labels