Page tree
Skip to end of metadata
Go to start of metadata

Access through SQL

Play SQL stores the data in clear text in the SQL database:

  • 1 Confluence space = 1 database schema
  • 1 Spreadsheet = 1 table

It is possible to access the data in read-write mode, especially:

  • You can query and export the data using any SQL client,
  • You can create and delete records in your tables.

Therefore it is possible:

  • For scripts to insert data automatically in Play SQL's database,
  • For other software to read data from Play SQL

Also, please note that Postgres can read data from foreign databases as if they were local tables. See How to access foreign databases from Postgres.

Download as an Excel spreadsheet

Since  PLAYSQL-174 - Getting issue details... STATUS , we've introduced an export into Excel. The best way to full access your live data remains SQL. We provide the Excel export for easier integration with desktop tools.

  • Want to export several spreadsheets? Play SQL creates a full workbook with all spreadsheets (1 tab per spreadsheet).
  • Data and columns only are exported,
  • Formulas, audit trail, settings aren't exported. The feature is provided as one way to manage the data in Excel, but a full SQL access allows full access to all your data in read-write mode and in clear text.

Note that exporting a full table can draw the server's resources and exporting a full space can be more difficult, depending on the amount of exported data. Therefore this feature is only available for administrators. There are options for system administrators to change the default timeout (60s) or disable the feature. See  PLAYSQL-174 - Getting issue details... STATUS  for more information.

Copy / Paste

An excellent way to import/export data to/from Excel is to copy and paste rows. Draw an area on the spreadsheet, press Ctrl + C, and the data can be pasted in Excel. 

Tips:

  • Do not enter a cell in edit mode before copying/pasting, otherwise you might copy the contents of that cell only. Just select one cell with a single click, then press Ctrl + C.
  • Play SQL creates rows and column if you paster larger data than the displayed table.
  • Play SQL pastes with formulas if the data comes from a Play SQL spreadsheet.

PDF Export

When exporting a page through Confluence's PDF Export feature, Play SQL tables are included in "PRINT" mode. Starting from version 2.11.1, it is possible to style them using Confluence's PDF stylesheets. Example:

PDF export stylesheet to remove the first column of all Play SQL tables when printing
.confluenceTable.playsql-print-table tr > *:first-child {
    display: none;
}
 Click here to see the HTML of a PDF export...
    <table class="confluenceTable playsql-print-table" id="space:{spaceKey}-{spreadsheetId}">
        <thead><tr>
               <th class="confluenceTh">#</th>
               <th class="confluenceTh">Column 1</th>
               <th class="confluenceTh">Column 2...</th>
        </tr></thead>
        <tbody>
                <tr>
                    <td class="confluenceTd">1</td>
                    <td class="confluenceTd">Contents</td>
                    <td class="confluenceTd">Contents...</td>
                </tr>
            (...)
        </tbody>
    </table>

 

 

  • No labels