Highlight of the release
- Joint Tables (augmenting a read-only table with comments)
- Open-source dialects
- Create from the Create dialog
- Move storage for queries
- New pricing
We're introducing an Excel-like language for formulas at a cell level. Have you ever wanted to generate a value instead of writing it?
Ergonomy was a focus for this feature. We wanted users to instantly be able to write formulas. They can discover functions by typing keywords. If they know "MATCH()" in Excel, simply typing the name will suggest the "LOOKUP" function in Play SQL. We also help them autocomplete column names or table names:
Pasting the formula on the full column will apply it to all visible cells. It is not possible yet to apply the formulas to future cells or non-visible cells, and we'll work on this for the next version.
More information about the formula language: See Formulas 2.
Note about Totals and CLF (Column-Level Formulas)
Users used to be able to write SQL formulas in the totals and at a column level. Those weren't popular, since they didn't have autocomplete and weren't easy to use. They are still available in this version under the name "CLF formulas". We plan to migrate them to the easier, new language in a future version.
Play SQL still supports external modification by other SQL tools. Formulas will be executed if the program hits the proper REST resource after update.
Augment read-only data with Joint Tables
Some data is read-only. It could come from your CRM or from another internal tool. It has always been possible to display it in Play SQL. Now you can even add columns to comment it!
Rows on the right-hand side are created on request, when the user starts typing or clicks the + / - buttons:
It takes a few steps:
- With Postgres 9.3, create a Foreign Table. The system administrator sets up the database to display contents from an external source as if it were a local table.
- Create a spreadsheet named "Comments for my foreign table". Make sure you give it a "foreign key" column of type Integer. The ID of the read-only record will be written in this column by Play SQL. For example, the table can contain 3 columns: ID, Name (text), FK (integer).
- Create a Joint Table, where you specify the read-only table and the "Comments" table.
- Done! Users can enter data in the Comments table and see it alongside the read-only data, as above.
The dialect is the language used by programs to connect to the database. Specific dialects were created for Play SQL since no other dialect could provide access to table metadata. We're happy to open-source them today.
There is no immediate change for users, since all current users have Postgres. Customers who have a strong desire to support MySQL, Oracle or MS SQL Server can check out the API and contribute to the project themselves. We'd be pretty happy if it happens, or if the library is reused in another open-source project. Dialects for Play SQL can be developed internally or distributed through the Atlassian Marketplace, independently from us, so there is an opportunity to share the costs of development.
Dialects are divided by capabilities, and Play SQL reports which dialects are installed on your instance of Confluence:
Only Postgres has full transactions over DDL. MySQL, MS SQL Server and Oracle don't have support for transactional DDL. Therefore it will never be possible to implement full read-write capabilities in other vendors than Postgres. We've always relied on it because it's open-source, it has all the features we've always dreamt of, and it has a well-designed structure.
Contribute to the Dialects project on Bitbucket
We've improved the Create experience by adding Play SQL to the Create dialog:
There is one technical change on this release: On read-write datasources, queries are stored in the database. We find that this is more consistent with the user experience when a new space opens an existing datasource.
The impact for Play SQL Base is, it keeps storing queries in Confluence metadata. Play SQL can actually read both. Queries in the database are numbered above 1000.
We're happy to announce a change of pricing. We've noticed that small instances provided a negligible income for us. So we simply decided to lower their price to almost-free: $10 for the first 50 users!
|Tier||Old price||New price|
|Privilege tier||10 Users||$29||$10|
|Privilege tier||25 Users||$198||$10|
|Privilege tier||50 Users||$398||$10|
|Normal price||100 Users||$798||$698|
|Normal price||250 Users||$1398||$898|
Obviously the model is that larger companies are paying for smaller ones, but the maximum is $8 per user. Besides, driving more evaluations for small instances will develop the ecosystem around Play SQL Spreadsheet.
We haven't made a major release for a few months because the formulas story had several chapters that needed to be complete before publishing. We've made several small improvements about keyboard navigation and edition.
Upgrade guide for system administrators
Please read Release Notes 2.10 - Upgrade Guide for more information about technical points.
- If you upgrade from Play SQL 1.4.1: Please upgrade to 2.3.x first, otherwise your queries will be unavailable. If you've upgraded too soon, please reinstall Play SQL 2.3.x, then upgrade to the latest version.
- If you upgrade from Play SQL 2.6, there are new dialects instead of Generic/Unsupported, for those who don't use PostgreSQL. Please switch to:
- Oracle (Unsupported)
- MySQL (Unsupported)
- The only officially supported database is PostgreSQL.
- If you upgrade from Play SQL 2.6, there are new requirements:
- Requires Java 7,
- Requires PostgreSQL 9.0 (Query monitoring requires 9.2, Foreign Tables require 9.3),
- Requires Confluence 5.3.
- If you upgrade from Play SQL 2.9, there are schema changes:
- You may wish to back-up your database before upgrading.
- See Release Notes 2.10 - Upgrade Guide for more information,