Release Notes

Version 1.7.5 (2024-11-09)

• Have the Open Details action (Alt+Enter) automatically open the detail view in a sidebar, without the user having to drag its tab. Adjust focus behavior when closing the sidebar.

• Adjustments to the Custom Join action. Prompt if no table or constraints are selected.
• Improve alignment of text and icons in the field selector, and other UI cosmetics.
• Some terminology adjustments, for consistency with an upcoming documentation site.
• In generated SQL code, make identifiers more readable (e.g. c42_price instead of c42).
• Fix automatic selection of new items in the Folders pane, e.g. after adding a data source.
• Fix a bug where the formula bar or inplace cell editor did not always update after editing.
• MacOS: Fix sluggish graphics on certain external monitor configurations.
• Windows: Fix possible repaint issues on certain graphics cards.

Version 1.7.4 (2024-09-24)

• Improve the display of errors that occur while attempting to list the tables in a data source in the Folders pane. For example, a CSV file might have been moved or deleted. The relevant error message now displays in red next to the data source icon, instead of triggering an "unexpected" error. You can hover over the text to see the full message.
  
• Fix a cosmetic issue in the cell editor on Retina/HiDPI screens (leftover cursor pixels).
• Adjust auto-formatting to avoid decimals for very large numbers.
• Improve formatting of the row count indication in the toolbar.
• Fix a checkbox bug when changing advanced properties in the Fields sidebar.
• MacOS: Make F2 work to rename items in the Folders and Fields sidebars.
• Windows: Switch to a code signing certificate provided by Microsoft.

Version 1.7.3 (2024-08-20)

Faster Aggregate Queries

For large databases, many queries involving aggregate function (SUM, AVG, MAX etc.) now run substantially faster. This applies in particular when using the Custom Group feature.

For example, a simple SUM over a PostgreSQL table with 10 million rows, grouped by a single text field, would previously appear to hang indefinitely, but now completes in about one second on a MacBook Air M3 laptop:

The improvements are achieved through simplifications in the SQL queries that Ultorg generates, for the most common kinds of aggregate queries. The actual result of each query remains unchanged. Ultorg has a large suite of test queries that run with optimizations enabled and disabled to check that query results remain the same from release to release.

A formula containing a SUM over another SUM formula (grand total/subtotal) will now usually compile to the same SQL as a SUM directly over the innermost referenced value.

Some aggregate queries may still be slow on large datasets, in particular when sorting on the aggregate result while simultaneously showing the input rows of the aggregate. In such cases it may help to hide the input rows, or to add a filter to reduce the amount of data. Additional optimizations may be introduced in future releases.

MacOS Improvements

• Fix two-finger tap to right-click, where configured (was broken on some MacBooks).
• Update the app icon to conform with Apple's design guidelines.
  
• Fix blurry tooltips for long file names in the Folders pane.
• Cosmetic adjustments to button widgets and UI font weight, to match the MacOS style.

Other Updates

• Linux: Bundle icon files for the Ultorg application, and provide instructions for creating a desktop shortcut on Gnome or KDE (on the Download page).
  
• Windows: Avoid slightly sluggish popup menus.
• Fix unreliable drag-and-drop of files to the Folders sidebar (e.g. to connect to a CSV file).
• Improve the type-to-search feature that is available in the Folders and Fields panels, so it behaves more like Ctrl+F search in Chrome and in Ultorg's perspective editor.
  
• Show the Update Available message only every few days, with gradually increasing delay.
• Adjust keyboard focus handling when switching to Ultorg from another app.
• Adjust field selector behavior for checkboxes associated with a compact join.
• When bulk pasting to insert rows, avoid skipping empty rows and columns in the input.
• Adjust actions and keyboard shortcuts in the SQL/JSON Output tabs.
• Update various underlying software libraries.
• Additional small user interface adjustments.

Data Source Specifics

• Oracle: Fix an error that prevented Apply Edits from working in some cases.
• Microsoft Access files: Support the extended date/time type, introduced in Access 2019.
• SQLite: Implement the int function.

Version 1.7.2 (2024-06-05)

Add the Data→Replace Data Source action, which changes the data source that is referenced by the current perspective, without otherwise modifying the query. This can be used e.g. to switch between production and development environments.

For perspectives that include tables from multiple data sources, the cursor position is used to determine which data source should be replaced.

This action is primarily useful when the old and new data sources are of the same type and contain tables and columns with identical names. Future versions might add more options.

Other Improvements

• When applying data edits, perform DELETE operations before INSERT, in case the database has uniqueness constraints that could otherwise conflict.
• When switching to Ultorg from a different window, move focus to the perspective editor.
• Avoid a warning that could appear if insert row/equals were pressed in quick succession.

Version 1.7.1 (2024-05-10)

• Make bulk paste work with Logical (boolean) and JSON-typed columns.
• For bulk paste into text columns, avoid parsing of pasted values as dates or numbers.
• Fix a StackOverflowError from certain scroll interactions in sidebars or popups.
• Fix a bug from 1.7.0 where table extracts would incorrectly display as "Recovered".
• Fix an error when the formula bar drag area is accidentally right-clicked.
• MacOS: Cosmetic adjustments to popup menus.

Version 1.7.0 (2024-05-02)

Google Sheets as an Editable Relational Database

Data in Google Sheets can now be edited directly from Ultorg, and you can build a complete relational database out of Google Sheets worksheets.

This means you can use all of Ultorg's functionality without an external SQL database, and for collaborative work. Some users may be accessing data from Ultorg, and others from the Google Sheets web app. You can collect data using Google Forms, too.

To make a Google Sheets spreadsheet editable from Ultorg, organize it as follows:

• Each worksheet (spreadsheet tab) should contain a single table of data.
• The top row in the worksheet should contain names for each column.
• Each table column should contain data of one type only: text, numbers, dates, times, timestamps, or true/false. If you are designing a new table, you can provide a row of example data so that Ultorg can detect the column types.
• The leftmost column should contain values that uniquely define each row. These identifiers can be numbers (1, 2, 3, ...) or text (airport codes, JIRA tickets etc.).

  If the name of the first column is ID, and you use numeric identifiers, then Ultorg will assign auto-incremented values when you insert new rows. Prefixed numbers, such as DEPT-3215, can also be auto-incremented.

For examples of properly organized Google Sheets spreadsheets, see the links below. To try data editing with these in Ultorg, first make a copy to your own Google Drive.

• Simple CRM Database: Tables illustrating how Ultorg could be used as a Customer Relationship Management tool, letting you track customers and interactions with each customer (emails, meetings etc.). There is also a table containing data collected via Google Forms, which can be joined to the Customers table via the Email Address field.
• Course Catalog: A database of academic courses, with instructors, meetings, and reading lists. About 12,500 rows across 5 related tables.

Data Modeling

The use of an ID column in each table—a so-called primary key—ensures that data can be edited safely by multiple users at once, even as rows are inserted, deleted, and reordered. Tables without a primary key column can still be used in Ultorg, but are not editable. In Ultorg, a table's primary key column is indicated by a key icon () in the Fields sidebar:

In a relational database, it is common to have the IDs of one table be referenced by columns in other tables. For instance, a Customer Interactions table might have a column called Customer ID, which references the ID column of the Customers table. This is a powerful concept that allows you to model all kind of business relationships in a standardized way. You can use Google Sheets to design your tables, and then tell Ultorg about relationships between them using the Custom Join action:

With joins configured between tables, you can tailor Ultorg perspectives for your various business tasks, and use the same perspectives for data entry. For example, you can log a new customer interaction by placing the cursor on a cell under Customer Interactions, next to the desired parent row in Customers, and invoking Insert Record (Ctrl+Shift+Plus):

Fields that are joined against another table's primary key column will provide a drop-down menu when edited (as shown in the demo animation at the top of this post).

Connecting and Refreshing

To connect to a Google Sheets workbook in Ultorg, click Add Data Source→Connect to Google Sheets and follow the instructions. Ultorg perspectives can combine data from any number of Google Sheets workbooks, and other kinds of data sources (e.g. local Excel or CSV files).

If you change the structure of a database table, such as data types, column names, or the format of the primary key column, click Data→Refresh Table List/Metadata to have Ultorg detect the changes. To refresh data only, use Refresh Data (/Ctrl+R).

More Details

• Ultorg's Google Sheets integration is designed to permit concurrent edits by multiple users, provided that column definitions and primary key values remain stable. Blank or reordered rows may occur in some cases; this is normal.
• The Apply Edits action will perform sanity checks on affected sheets before proceeding to make changes. This includes checking for the continued presence of relevant rows and columns, and preventing duplicate values in the primary key column.
• Auto-incremented IDs may skip numbers that were previously assigned to now-deleted rows. This is by design. Very large gaps are avoided.
• Editing of dates, times, and timestamps should work regardless of spreadsheet locale.
• As before, the actual execution of database queries happens on your local machine, using Ultorg's bundled PostgreSQL engine. Interactions stay fast even on slow Internet connections. You can make queries and stage edits even while temporarily offline.

Other Improvements

Data Editing

• Make the Fill Down action work in partially loaded groups and on primary key fields, when the cursor is on an inserted record.
• Make checkbox fields (boolean/logical fields) easier to use when inserting new rows, by setting them to an explicitly unchecked (false) state by default.
• Show the progress indicator while waiting for extracts to refresh after a data edit.

Miscellaneous

• Add Open in Google Sheets and Open in Excel actions in the context menu that appears when you right-click a relevant data source.
  
• Have Refresh Table List pick up table names changes from the data source.
• For users on MacOS 14.4.0, suggest updating to MacOS 14.4.1 or later.
• Improve various error messages and informational messages.
• PostgreSQL: Make the SINGLE formula function work on UUID values.

Version 1.6.2 (2024-04-18)

Limited release for a subset of customers. All features below were later rolled into Ultorg 1.7.0.

Microsoft SQL Server

• Make integrated Windows authentication work out-of-the-box (#14). This allows passwordless connections to Microsoft SQL Server via Active Directory or the local Windows user. Ultorg now bundles the necessary DLL file.

  To connect to Microsoft SQL Server using integrated authentication, use a JDBC URL in the following form, with an empty username and password:

  jdbc:sqlserver://localhost:1433;trustServerCertificate=true;integratedSecurity=true

  (Use the actual host name of your database instead of localhost.)

• Bump the SQL Server JDBC driver version to 12.6.1.
• Switch the default transaction isolation level to READ COMMITTED, as higher levels are disallowed for certain table types.
• Fix an error that occurred with table columns of the geography type.

PostgreSQL

• Make integrated Windows authentication (SSPI) work out-of-the box. This allows passwordless connections to PostgreSQL via Active Directory, when this has been configured on the server. Ultorg now bundles the necessary DLL file and libraries.

  To connect to an SSPI-enabled PostgreSQL server from Ultorg, enter your Windows username and an empty password. No special JDBC configuration is needed.

• Bump the PostgreSQL JDBC driver version to 42.7.3.
• Avoid an error on startup if the user adds a custom JDBC driver for PostgreSQL.

Visual Query System

• Load a larger number of rows when the user first scrolls to the end of loaded results.
• Adjust the filter that is proposed from Ctrl+F search on a JSON column.
• Unsuppress previously canceled connection attempts when Refresh Data () is invoked.
• Adjust extract selection rules for locally executed queries. When custom table extracts are present, prefer extracts with fewer columns, and filtered extracts over unfiltered extracts.
• Properly display extract loading errors that affect the currently displayed perspective.
• Improve logging for certain JDBC connection errors.

Other Improvements

• Preview of data editing in Google Sheets. (Officially released in Ultorg 1.7.0.)
• For ZIP file distributions, include the Ultorg version number in the root folder name.
• Add some unadvertised alternative keyboard shortcuts.

Version 1.6.1 (2024-02-29)

• Fix an UnsupportedOperation error that could appear when filter indictions grew too long.
• Handle Ctrl+L to set a filter when Find is open with the cursor on a compact join value.
• Smaller adjustments to functionality introduced in Ultorg 1.6.0.
• Update runtime library versions.

Version 1.6.0 (2024-02-19)

Browser-Style "Find" Action (Ctrl+F)

The Find action (/Ctrl+F) lets you quickly search in the currently loaded perspective. To move the cursor forward or backward between matching cells, press Enter or Shift+Enter:

Matching characters are highlighted in yellow, while each cell with a match is highlighted in brown. Press Escape to close the Find bar and remove the highlights. Or press Up/Down/​Tab/​Shift+Tab to restore focus to the editor area while retaining highlights.

By default, all visible cells are included in the search, including column labels. To restrict the search to data values from specific columns (fields), select them first. Search highlights are still shown across all cells, but pressing Enter will move the cursor only within the selection:

In Ultorg, field selections can be done by holding down Ctrl and clicking the column label, or by pressing Ctrl+Space. (On MacOS, use the Command key instead of Ctrl.)

For cells containing numeric, date, or time values, the search term is matched against the actual text that is displayed in the cell, after formatting has been applied. For text values, the original text is searched without modification.

If the search term starts with a double quote, Ultorg will accept C-style backslash escape codes to represent special characters. For example, the search term "\n" (with quotes included) can be used to search for line breaks in text values:

When working with large datasets, there will often be data in the database that is not immediately available for searching via Ctrl+F, as Ultorg loads only enough data to fill the window by default. Rather than scrolling through thousands of rows, you can press Ctrl+L (⌘L on MacOS) to quickly set a filter based on the current search term:

The filter is set on the field that the cell cursor is currently located on. For text fields, a case-insensitive "contains" filter is set, with the same search condition as is used when matching cells with Ctrl+F. For numeric fields, an exact match is required. For date and timestamp fields, you can type a partial date to search for a range of dates or times, e.g. 2023-11 to find all timestamps in November of 2023:

Both quoted strings and date ranges are also available from the Filter toolbox. A more limited Ctrl+F search is also available in the Folders and Fields sidebars.

"Open Details" Action

The Edit→Open Link/Open Details action () provides a fast way to jump from a table row or foreign key value in one perspective to a "detail view" in a different perspective.

Place the cell cursor on a table row or foreign key value of interest, and press Alt+Enter (Option+Enter on MacOS). Ultorg will ask for a perspective to use as a detail view for the data table in question. When you click OK, Ultorg will open the detail view and apply a filter such that the originally selected record becomes visible:

The default detail view shows all fields in the data table using a single-column form layout. Joins and formulas from the original perspective may also be included. An open detail view will be reused automatically until closed.

If desired, you can customize the detail view using any of Ultorg's regular query and formatting actions. For example, you can include data from related tables via Query→Fields & Joins, or switch to a multi-column form layout using Format→Set Auto-Layout Type. Then click File→Save Perspective to save the modified perspective.

To qualify as a detail view for a given data table, a saved perspective must use said table as its data source at the root level, and must use a non-tabular auto-layout type.

It is often useful to view data side-by-side, with one table view and one detail view:

To set up a split pane like this, drag the detail view's tab to the side of the screen. You can also move a tab to a separate window, by right-clicking the tab and clicking Float.

If the currently selected cell contains a URL, the Alt+Enter shortcut will open the link in an external web browser, as in previous Ultorg versions.

Improved "Fill Down" Action

The Edit→Fill Down action (Ctrl+D) will now work when the cursor is on a deleted or inserted record. For deleted records, records below the cursor are marked for deletion as well:

For inserted records, the value under the cursor is filled down first to the end of the inserted records, and then to remaining records in the group:

The Apply Edits operation now avoids unnecessary network delays when there are many edits.

Checkbox Fields

Fields containing true/false values, also known as "boolean" or "logical" fields, now show their values as checkboxes:

In Data Editing mode, you can click the checkbox to toggle it, or select it and press Space.

If the field supports a null value, separate from false, you can use the Delete key to produce it, as for other data types. Null values are indicated by the absence of a checkbox.

For Ctrl+F search purposes, a checkbox cell matches the word true or false.

Formula Bar Improvements

You can now drag the formula bar to enlarge it, like in Excel or Google Sheets:

Clicking the formula bar icon will open the cell editor and auto-complete dropdown if available.

When empty, the formula bar will now show an explanatory message in grey. A cell with a null value will show the message "null value" or, for date/time fields while in Data Editing mode, the expected format for data entry:

Non-standard data types are now indicated with their name from the external data source:

Retrieve More Rows

During interactive queries, Ultorg will normally limit the number of rows retrieved, and increase the limit only if the user scrolls to the bottom of the currently displayed result.

This behavior can now be overridden, by opening the Format popup on the root label of the displayed result (the topmost bold heading) and changing the Retrieved Row Count property:

Retrieving a large number of rows may slow down query interactions, but may be useful for long-running queries. If you have a long-running query that returns a flat table of results, you may consider using File→Create Extract to create a snapshot of the result instead.

Performance Improvements

This release includes various changes intended to improve interactive query performance.

• Use fewer SQL queries to retrieve data for each Ultorg perspective.
• Eliminate one case where an aggregate calculation was unnecessarily duplicated.
• Provide JDBC drivers with a suggested fetch size in more cases.
• Avoid triggering a new query when the user merely hides a field that is already showing on the screen, or when the same field is subsequently reintroduced. (With some exceptions.)
• Avoid triggering a new query when a formula is inserted but has not yet been defined.
• Eliminate a brief UI lag when loading perspective data with many rows.
• Oracle: Disable the workaround for Oracle bug 6653652 on unaffected versions.
  (2024-02-07: This was reported to fix slow table list retrieval.)
• Oracle: Add more logging around table list retrieval.

(If you notice significant improvements or degradations in query performance from this release, we would love to hear about it.)

Filter Improvements

A summary of filter state is now shown in the Fields sidebar:

When filtering on dates or timestamps, you can enter e.g. "30" in the search field as a shortcut for the date 30 days in the past:

A filter set this way will still be defined with respect to the specific constant date that is shown in the list. To create filters or formulas that change depending on the actual time that the query is executed, you can use the now or today functions, which are added in this release:

In the example above, a formula is added that calculates the number of days since each log event occurred. The user could then set a filter on the calculated field e.g. to show log events that occurred in the last 14 days.

Other Improvements

Visual Query System

• Adjust checkbox behavior in the Fields pane when Show Group Hierarchy Only () is enabled.
• Add the abs (absolute value) formula function.
• Improve various UI messages.
• MacOS: Fix an always-disabled Data→Show Exact Row Counts menu item.

Layout System

• Improve selection and highlight color blending on Dark Mode.
• Smaller layout adjustments (column justification, baseline alignment, edit indications).
• Show blue cursor highlight indications on both the left and the right edges of the editor, and make them more visible:
  

Data Sources

• For external database connections, add a Disconnect action that can be accessed by right-clicking the data source () in the Folders pane.

  (Ultorg manages connections automatically, and disconnects after 15 minutes of inactivity. Explicit disconnect is useful primarily when external software depends on it, e.g. if a maintenance script is waiting for connections to close.)

• Fix one deadlock and one benign error that could occur when stress testing extract refreshes and the Run Queries on Local Data Extracts feature.
• Have the File→Create Extract action handle conflicting column names in one case.

Version 1.5.0 (2024-01-18)

Feature Discoverability: Populate the Menu Bar

In previous Ultorg versions, a lot of functionality was hidden away in context menus and toolbar buttons, making it hard to discover. The main menu bar has now been reorganized and populated, so that all important actions are represented:

The context-sensitive behavior of actions has been improved in various cases. All remaining icons now display in high resolution on Retina/HiDPI screens.

Run Queries on Local Data Extracts

When connecting to an external relational database, you can now request that Ultorg execute queries on your local computer, rather than on the external database. This will often improve query performance, and reduce load on the external server.

(In earlier Ultorg versions, local query execution was used in limited cases only, for queries involving multiple data sources. It can now be enabled for any external database.)

You can find the new Run Queries on Local Data Extracts option under Data→Other Data Source Options:

To facilitate local query execution, Ultorg creates a local copy (extract) of each database table that is used in a visual query. This is done the first time each table is used in a query. Ultorg will show extract loading status next to each original table in the external data source:

Trivial queries, which merely show the contents of a single table, are still executed remotely.

The Data→Refresh Data action (/Ctrl+R) can be used to refresh all extracts relevant to the current perspective. You can find stored extracts in the Generated Extracts folder.

"Auto-Format Field Names" Setting at the Data Source Level

As in earlier Ultorg versions, table and column names can be auto-formatted for display purposes. For example, a field that is named START_DATE or startDate in an external database may be shown as "Start Date" in Ultorg.

You can now disable or enable such auto-formatting at the data source level, via the Auto-Format Field Names setting in the Data→Other Data Source Options dialog (#56):

This setting is enabled by default for relational database connections, and disabled by default for other kinds of data sources.

The setting can also be overridden in individual Ultorg perspectives, via Format→Auto-Format Field Names. This replaces a setting previously known as "Prefer Technical Field Names".

Data Editing

The Insert Record () and Delete/Revert Record () actions are now represented both in the Edit menu and with new icons in the toolbar:

The new Fill Down action (seen in the Edit menu above) lets you bulk-edit a range of cells, by copying the currently selected value to the cells below, until the end of the current group. You can use filters, joins, and other query functionality to help select the rows to be updated.

When editing date or timestamp values, a Current Time option is now shown in the dropdown:

Layout Actions

The Crosstab () action, in the toolbar or the Format menu, will now work from a larger range of possible cursor positions. If a crosstab cannot be enabled for the current selection, an informational message is shown:

A new Remeasure Layout action (in the Format menu) forces table column widths and other layout measurements to be recomputed based on currently displayed data. The new Clear Formatting action resets any overridden formatting properties. These actions apply to the current perspective, or any explicitly selected fields.

Keyboard Shortcuts

Many keyboard shortcuts have been added. The shortcuts are displayed next to the actions in the menu bar, and in context menus. In particular, the Alt+Right shortcut for opening the context menu (Option+Right on MacOS) should be useful during visual query manipulation.

On Windows and Linux, menus items can additionally be accessed via customary underlined letter shortcuts, which are displayed when the Alt key is held down:

Formatting options that were previously available only through drop-down menus in the toolbar can now be operated from the keyboard. See the shortcuts in the Format menu.

Other Improvements

Visual Query System

• Adjust the behavior of checkboxes in the Fields popup, for the case when all primitive fields are visible, e.g. right after a table is opened by double-clicking it. In this case, show all fields as initially unchecked, to make it easier to select a few specific fields for display.
• Add Binary as an officially supported data type, for sorting, filtering, grouping, editing etc.
• When a visual query yields errors and no data, show the errors with any previously received data. This is useful e.g. while experimenting with rawSQL* functions.

Data Sources

• Automatically disconnect external database connections after 15 minutes of inactivity. This reduces timeouts and long reconnection delays.
• When adding a new database connection, allow new JDBC drivers to be added directly from the Connect to Database dialog. (This replaces the old "Services" pane.)

• Avoid ON TRUE clauses in generated SQL queries. This may improve performance on some backends.
• Fix one error condition in the Export to CSV action.
• Oracle: Retrieve the table list in fewer network round-trips, reducing latency.

Miscellaneous

• When copy/pasting a Compact Join value to an external application, and a single field is selected for display from the referenced table, copy the displayed value rather than the value of the key.
• Add the Help→Check for Updates action.
• Reduce message update delay in the Application Log pane (in Help→Diagnostics).
• MacOS: Have the title bar brightness follow the system dark/light setting.

Version 1.4.2 (2023-11-26)

Import/Export Perspectives

Ultorg perspectives (), and associated data source configurations (), can now be exported to a file for import on other computers (#32). This allows you to share a curated folder of perspectives with other users, or otherwise save them for later use.

You can export either the entire Local Ultorg Folder () or a regular folder () that you have created yourself in the Folders pane.

If the exported folder contains data source configurations, then the configuration data will be included in the exported file. This includes, for instance, database host names and user names, but excludes passwords and authentication tokens. If the exported folder contains perspectives without their associated data sources, then the user will be prompted to re-create any missing data sources when a perspective is first opened. In any case, the name of each original data source is stored with each perspective, to facilitate later re-creation.

Visual Query System

• Add a special set of formula functions (rawSQL*) to allow custom SQL expressions to be used from within an Ultorg perspective . This can be used to work with data types and database functions that are supported by the underlying data source but which have not (yet) been included in Ultorg's standardized list.

  For example, the formula below uses the rawSQLaggNum function to access the aggregate function percentile_cont on PostgreSQL:

• In formulas, allow the min, max, year, and month functions to be used directly on Global Timestamp values. Timestamps with time zone offsets are adjusted to UTC.
• For date and timestamp fields, show a context menu shortcut for adding a max aggregate.
• When double-clicking a table causes an existing tab to be focused, refresh its perspective.
• Generate more efficient SQL for queries involving large tables without a primary key.

Other Improvements

• Google Sheets connections: Verify Ultorg with Google, to avoid a warning when connecting. Improve error handling. Existing connections may need to be re-authenticated.
• Data editing: Adjust column-sizing to improve layout stability during data editing.

Version 1.4.1 (2023-11-15)

• Make aggregate queries work even when grouping by a field of an unrecognized data type.
• Further adjust a previous filter options adjustment (behavior when no options are found).
• PostgreSQL: Properly support the citext type on PostgreSQL, so it can be filtered on, etc.
• PostgreSQL: Run interactive queries in repeatable-read rather than serializable transaction isolation mode. (Experiment for this release only; was reverted in version 1.4.2.)

Version 1.4.0 (2023-11-11)

Ultorg is now out of beta and available to the general public. A great thanks to our early adopters, who battle-tested the earlier versions and gave us feedback along the way!

User Accounts & Activation Flow

The Ultorg website and the Ultorg desktop software have been updated to incorporate an account creation and license activation flow. New and existing users can now:

1. Create a user account on the Ultorg website.
2. Download the Ultorg desktop software.
3. Start a free trial period.
4. Purchase a license to continue using Ultorg after the trial period.

In the future, the user accounts system will also be used to enable collaborative functionality, such as sharing of perspectives and data sources.

In the Ultorg desktop app, a new activation system links your installed software with a trial period or paid license from your user account. Activation can be done either online or offline; an Internet connection is no longer required to run Ultorg (#57). The new activation system should also work better with certain corporate proxy servers.

After startup, product activation status can be accessed again via Help→Product Activation.

Multi-User Licensing

You can purchase Ultorg licenses for multiple users on a single invoice, and manage your users from the License Assignments page:

Support and Feedback Dialog

The Support and Feedback feature, accessible from the Help menu, lets you quickly submit technical questions and product feedback. Both are very welcome—your messages will help us continue improving Ultorg and its (soon-to-be-written) documentation!

Data Editing Improvements

• Bulk paste from Excel: In Data Editing mode, you can now paste multiple rows at once from Microsoft Excel, or similar tab-separated formats (#31). The pasted data is automatically parsed and mapped onto the currently visible columns, starting from the column of the selected cell. Alternatively, you can multiple-select specific columns to use before pasting. All target columns must be from the same table in the underlying data source.

  If the pasted values are not compatible with the data types of the target columns, a friendly warning tooltip will be shown.

  The pasted rows appear as pending new rows to be inserted. (In the future, we may add an "overwrite" option as well, where pasted data goes into existing rows instead.)

  
• Improved error dialog: After invoking Apply Edits, you may sometimes need to examine validation errors that are emitted by the external data source. The error dialog box has been improved to make it easier to read and select/copy the message:
  
• Add keyboard shortcuts for Toggle Data Editing (Ctrl+Shift+E) and Discard Pending Data Edits (Ctrl+Shift+D). Add the latter to the File menu.
• Take ongoing data edits into account when calculating column widths and formatting.

Visual Query System

• When the user opens a data table, prefer to activate an existing tab instead of always creating a new perspective, if the existing tab shows an equivalent table view.
• Fix an application freeze observed when a user connected to a database with 30,000 tables (#60). (We may further improve performance for this case in the future.)
• Prevent performance problems due to very long values in a single cell.
• Adjust displayed filter options in some cases where the list would previously be empty.

Data Sources

• Allow file-based data sources to be added by dragging the file from the Windows/MacOS/Linux file explorer to the Folders sidebar in Ultorg:
  
• Update the bundled JDBC driver versions for PostgreSQL, MySQL/MariaDB, Microsoft SQL Server, and SQLite.
• Adjust handling of the MAX_EXECUTION_TIME error on MySQL.
• On PostgreSQL, properly handle enum types that are not in the public schema.
• On PostgreSQL 16, have the jsonParse formula function return null for invalid JSON.

Layout System

• Adjust auto-layout defaults so that grand totals and other singleton formulas are automatically gathered above the main table sublayout.
  
• Avoid auto-setting Show Date Only when Show in Local Time Zone is enabled.

General

• Allow existing Ultorg versions to continue working even if updates are available (#45).
• Upgrade various runtime libraries to the latest versions.
• Avoid a brief lag in the UI after Ultorg starts.
• Some context menu adjustments, and cosmetic tweaks in the UI.
• Switch to Amazon S3 as the hosting provider for Ultorg downloads.
• Windows: Sign MSI installers with an Extended Validation certificate. This avoids a warning from Windows Defender SmartScreen when running the installer.
• MacOS: Remove a left-over Preferences item from the application menu, and reset any settings previously changed here. (On Windows/Linux, this was done in Ultorg 1.2.7.)

Version 1.3.1 (2023-11-09)

A limited test release made in preparation for 1.4.0.

Version 1.3.0 (2023-07-25)

Data Editing

• In Data Editing mode, show an improved legend with the number of rows pending insertion, deletion, and updates. Show keyboard shortcuts if space permits.

• For UUID fields, show an option to generate a random value. (Omitted for foreign keys.)
  
• Avoid the "Not all edits are currently visible" state in more cases.

Timestamps

• Range filters, i.e. the From/Between/Before options in the Filter popup, now work on fields of the Global Timestamp type. As with the Local Timestamp type, you can enter a date, or a partial date such as "2023" or "2023-04", to set the range to a specific date, year, or month.
  
• In formulas, allow the less-than/greater-than operators (< <= > >=) to be used with values of the Global Timestamp type.
• For data sources that store an explicit time zone offset with each global timestamp (e.g. Microsoft SQL Server), less/greater comparisons are defined to evaluate at UTC. Grouping and IS function comparisons, however, will treat timestamps as distinct if their stored time zone offsets are different, even if the values represent the same instant at UTC.
• Add a formatting option to show Global Timestamp values in the computer's local time zone. The formula bar will still show the original stored value (e.g. in UTC on PostgreSQL).
  
• Some tuning of auto-formatting rules for timestamps.

Formatting

• In the Format popup, make number format examples sensitive to the current locale.
• For Binary fields, show values in groups of 4 hexadecimal bytes (e.g. a4f5d23e e6cd28b8). This improves readability e.g. for 160-bit SHA-1 hashes.

Visual Query System

• Make formula editing hints work when focus is in the formula bar, and on certain non-US keyboard layouts. (E.g. "To edit a formula, first move the cursor to a calculated cell.")
• Fix a bug where the Delete Custom Group action was not being shown in certain cases.
• Include an Unhide action in the context menu for the Fields pane.
• Adjust generated SQL queries to improve index usage in certain cases.

Version 1.2.9 (2023-07-08)

Improved Export Action

To export query results, the Export to CSV action now works from any perspective, and has an improved user interface. You can invoke it from the File menu, or by right-clicking a selection in the Folders sidebar.

• Multiple tables or perspectives can be exported in one operation, by holding down Ctrl or Shift while making a selection in the Folders sidebar (Command/Shift on Mac).
• Show a progress bar and status indication. The user can cancel the export at any time.
• Nested data, from joins and one-to-many relationships, will be "flattened" into a simple table that can be used with e.g. Microsoft Excel. The format is the same as is used when copying data from a perspective via Ctrl+A/Ctrl+C.
• When possible, exported data is retrieved in a single SQL query and streamed directly to disk. This means that millions of rows can be exported without having to fit in memory.
• The user can set the number of rows to export, and the format to use for numbers.

Sum/Count Action

For numeric fields, the new Sum action serves as a shortcut for creating a sum formula, at an appropriate position one or more levels above the field to be summed.

If the context menu is opened on a group label (bold font), a Count action is provided instead.

Expand JSON Fields Action

For JSON fields, the new Expand JSON Fields action will examine retrieved JSON objects and create one jsonValue formula for each observed JSON object field. This lets you interact with JSON object fields in the same way as with regular database columns; you can hide and show them from the Fields popup, filter/sort on them, and so on.

• Multiple levels of JSON objects are supported, though JSON arrays are skipped for now. Numeric fields are detected and automatically casted as such using the num function.
• To work with JSON data from a text column, use the jsonParse function to parse it first.
  • On PostgreSQL, the jsonParse function has been improved to return null for most (but not all) invalid inputs, rather than causing the entire query to fail.

Visual Query System

• Expose more relevant actions when right-clicking a field in the Fields popup or Fields sidebar. Handle the Open Context Menu shortcut (Shift+F10), and Enter/Escape.
  
• For the Custom Group action, omit some grouping level options that would not be meaningful.
• When using Custom Group on a compact join, reuse the selection of displayed sub-fields.
• In the Filter popup, hide the table header in most cases, to save space.
• Data sources: Minor tweaks to the SQL generator. Adjust tuning of the bundled PostgreSQL database. Update the SQLite JDBC driver version. Adjust SSL certificate settings on MacOS.

Layout System

• Add a formatting option to show numbers using scientific notation (#29). For extremely large numbers (>25 digits), use scientific notation by default.
  
• In the Indented Bullet List layout, make overridden sublayouts behave more intuitively.
• Include the Text Fit property in the Format popup for UUID fields.
• For times and timestamps, show fewer decimal places by default.

Data Editing

• Handle Paste (Ctrl+V) into a single cell, for the case when the cell editor is not open.
• Have Cut (Ctrl+X) clear the selected cell's value.
• On locales that use comma as the decimal separator: When typing or pasting numbers, accept either comma or period as a decimal character. (In formulas, periods are still required for now, as comma is used to separate function arguments.)
• When a compact join is configured to hide the underlying foreign key field, allow the key field to be omitted from the formatted dropdown list entry during editing as well.
• Avoid showing the "Not all edits are currently visible" warning in certain cases where the same data table appears multiple times in the same perspective.

Application UI

• Various adjustments to the Save Perspective dialog. Have each perspective remember the folder they were last saved to, for use as a default.
• Minor cosmetic UI improvements.
• Linux: Some bugfixes relating to window resizing.

Version 1.2.8 (2023-04-20)

Data Sources

• When connecting to Excel or Google Sheets workbooks, detect use of the "Freeze Rows" setting, and use it to determine the position of the column header row. This allows irrelevant content that may precede the data table to be automatically skipped.

• Fix an error that could occur when using XLSX files exported from Apple Numbers.
• Update the Snowflake JDBC driver to version 3.13.29, which fixes CVE-2023-30535.
• Improve behavior when connecting to databases that are not explicitly supported by Ultorg, by auto-detecting the SQL syntax that is needed for basic functionality such as Filter and Sort.

Version 1.2.7 (2023-04-02)

Prefer Technical Field Names

Note: This setting was reworked in Ultorg 1.5.0, and renamed to Auto-Format Field Names.

By default, Ultorg displays the names of tables and columns in an auto-formatted style. For example, a field that is named START_DATE or startDate in an external database will be shown as "Start Date" in Ultorg. By popular request, this feature can now be toggled on and off, via a button in the toolbar ():

The layout engine will still detect word boundaries for line breaking purposes.

Dark Mode

There is now an official "dark" color scheme, available from Window→Dark Mode in the menu bar. (Some users found an experimental version of this feature before; this release includes many color scheme adjustments.)

Data Sources

• When applying multiple data edits in a single transaction, ensure that an error in any step will cause the entire transaction to be rolled back (one bug fixed here).
• During CSV/TSV file parsing, skip certain ASCII art that may occur under the table header.
• Return to the official MariaDB JDBC driver (version 3.1.2), instead of a custom build.

Visual Query System

• Added the countif formula function, as a shortcut for sum(if([condition], 1, 0)).
• Fix a rare bug where a formula might not be editable right after editing a column name.
• Fix an incorrect warning message when deleting a field from within the Fields pane.

Layout System

• When a table is double-clicked to open a new perspective, reuse any existing text measurements (column widths etc.). This improves performance.
• Make the "Column Width" formatting property work with crosstab headers.
  (And hide the "Vertical Table Heading" property.)
• Adjust layout heuristics slightly to make wide line-wrapped cells more readable.
• Add a blue cursor highlight on the left viewport edge (seen in the last screenshot above).

General

• Remove the Module Manager, Updates, and Platform Options items from the Internals menu, and reset any settings previously changed here. Some of the features previously found here, such as the table of keyboard shortcuts, will need additional work before they can be officially exposed.
• Remove various unnecessary dependencies (decreasing download size by 14MB).
• Avoid auto-maximizing the window on ultrawide monitors.
• Other smaller tweaks to the user interface.

Version 1.2.6 (2023-01-18)

Windows Installer

On Windows, Ultorg now has a proper installation wizard, which will extract files and create Start Menu/Desktop shortcuts.

Uninstallation can be done from Add/Remove Programs. User data (e.g. saved perspectives) will still remain for future installations.

The installer is a standard Windows Installer package (MSI file). In enterprise environments, this package can also be used for unattended installations.

Open Link in Cell

If the selected cell has a link in it (e.g. https://www.google.com/), you can now press Alt+Enter (Option+Enter on Mac) to open it, like in Google Sheets:

There is also a new function available in formulas, called urlEncode, which can be used in combination with the concat function to construct links with query parameters based on data in the database. See the rightmost column in the screenshot above for an example.

Data Sources

• Experimental support for Snowflake connections (#46). To connect to Snowflake, follow the instructions on the Download Ultorg page.
• SQLite: Fix a "BigInteger would overflow supported range" error on certain tables.
• MySQL: Fix an "Unknown column 'DATETIME_PRECISION' in 'field list'" error on older server versions (#47). Include MySQL 5.5 in the automated test suite.
• PostgreSQL: Make the full test suite pass on older PostgreSQL versions (9.0 was tested).
• Microsoft SQL Server: Use the defined compatibility level to determine available SQL features.

Linux

• Improve text rendering on the KDE desktop environment (enable subpixel anti-aliasing).
• Fix blank grey window on the xmonad window manager.

Acknowledgements

We thank Mathias Breistein at Alicia A/S for implementing a Continuous Integration pipeline for Ultorg, using GitHub Actions. This helps us prepare new releases with fewer manual steps.

Version 1.2.5 (2022-11-30)

Queries and Data Editing

• MySQL: Properly handle the BIT/BOOLEAN/TINYINT type variants, e.g. when filtering.
• Generate fewer/more efficient SQL queries in some cases, e.g. by avoiding SELECT DISTINCT.
• Further adjustments to the Dropdown List feature (see Ultorg 1.2.2). Make Enter/Tab from the dropdown work like in a regular cell editor. Make dropdown items easier to click. Improve dropdown list behavior in the formula bar.

Other Improvements

• Windows: Support international characters in installation and user directory paths.
• Windows: Fix broken window state after connecting/disconnecting an external monitor.
• Windows: Fix startup problems after moving the installation directory.
• Include libraries which may be needed by JDBC drivers for SSL authentication (#44).
• Smaller user interface tweaks.

Version 1.2.4 (2022-10-24)

JSON Type Support

Ultorg now supports JSON as a basic data type in table columns and formulas (#12). JSON data is automatically pretty-printed in the user interface, in a style appropriate for the current text wrapping mode. In formulas, the jsonValue function can be used to extract primitive values from JSON data. These features work on PostgreSQL, MySQL, MariaDB, Microsoft SQL Server, and SQLite. (JSON support on Oracle is left for a future release.)

• On PostgreSQL, table columns of type json or jsonb are automatically recognized as JSON. On MySQL, the json type is recognized.
• You can use the formula function jsonParse to parse text as JSON.
• Sorting, filtering, grouping, and data editing on JSON columns no longer yields errors.
• Pretty-printing of JSON is enabled by default, but can be disabled in the Format popup.

Data Editing

• In Data Editing mode (), the Dropdown List feature (see Ultorg 1.2.2 below) has been made more discoverable. When a cell is selected in a foreign key column, or in a column that has been joined against another table's primary key, a small button is shown as an affordance to open the dropdown list. The sizing and alignment of the dropdown list has also been adjusted. This completes GitHub issues #19 and #36.
  
• Fix "Not all edits are currently visible" in tables with multiple primary key fields.
• Make the Edit Behavior properties visible in the Format popup when active, and improve their descriptions. Keep these properties read-only when not in Edit Table Defaults mode.

Other Improvements

• Experimental support for Exasol connections (#42). To connect to Exasol, follow the instructions on the Download Ultorg page.
• Fix a bug where queries were unnecessarily refreshed after closing the Filter popup.

Version 1.2.3 (2022-10-03)

Edit Table Defaults

You can now assign default formatting settings for tables in external data sources. To edit table defaults, right-click the data table () in the Folders sidebar and click Edit Table Defaults:

You can then right-click any table column and select Format to see relevant options, as when working in a regular perspective tab. You can also use the Sort actions to set a default sort order, or change the default display names of columns (as in some prior Ultorg versions).

Formatting defaults apply to all freshly created perspectives, as well as when adding a new join to an existing perspective.

Data Editing

This release further improves the Data Editing mode (), which lets you edit data in an external relational database from any Ultorg perspective.

• Dropdown list improvements: Adjust sizing and open/close behavior of the dropdown list.
• Compact join preview: Properly display the Compact Join format for cells with pending edits. The Compact Join format allows fields from a referenced table to be displayed as part of a foreign key column's cell formatting (see Ultorg 1.0.9). Making this work for cells with pending edits requires an extra database query. This is now handled seamlessly.
  
• Ensure reviewable edits: Ultorg will now refuse to apply pending data edits if there are edits that cannot be displayed in the current perspective. Such situations, with the status bar showing "Not all edits are currently visible", can arise e.g. if the user hides a column with pending edits.

  Certain kinds of edits that would be immediately non-reviewable are now rejected, with an explanatory warning message in each case.

• Improved discoverability: The "Not in data editing mode" warning can now be clicked to enable Data Editing mode. The status bar now shows the keyboard shortcuts to insert/delete records.
  

  The Ctrl+Shift+Plus/Minus shortcuts now work with the numeric keypad, and on various non-US keyboard layouts.

  Also ensure that the warning tooltip can be shown even when the cell editor was open.

• Read-only and "Last Modified" columns: Using the earlier-mentioned Table Defaults feature, you can now configure individual table columns as read-only (#40) or as fields that should be auto-filled with timestamp or username values for logging purposes (#37). These settings are accessed via the "Edit Behavior on Insert" and "Edit Behavior on Modify" properties in the Formats popup.
  

  Modified edit behaviors are immediately honored by all perspectives, new and existing, and can not be overridden by individual perspectives.

• Fix an error which could occur when editing from a perspective containing a crosstab.
• Allow Ctrl+A or Shift+Left/Right to be used for text selection when the cell editor is open.

Visual Query System

• Add a "Hide Other" action to quickly show only the selected field(s).
  
• In the field selector, when unchecking Select All, avoid hiding fields with a sort or filter.
• Fix two bugs relating to loading of previous Compact Join settings.

Other Improvements

• In the Folders sidebar:
  • Allow perspectives, and folders of perspectives, to be copied via Copy/Paste.
  • Make the default Create Perspective action work with multiple selection.
  • Adjust auto-expansion/selection of folders in various situations.
• Allow crosstab columns to be narrower if repeated heading values can be collapsed.
• Fix keyboard focus behavior when Alt+Tabbing to an undocked window.

Version 1.2.2 (2022-09-18)

Dropdown Lists for Data Editing

In Data Editing mode (), when editing individual cell values, Ultorg will now show a dropdown list with suggested values:

This feature is particularly useful when editing foreign key fields, where each value is expected to match a primary key value in a different table. If your database contains declared foreign key relationships, Ultorg will recognize these, and use them to retrieve suggestions.

(This feature was requested in GitHub issues #19 and #36.)

For databases without declared foreign keys, you can use the Custom Join action to get the same behavior. Right-click any column that you want to behave like a foreign key, click Custom Join, and join against the primary key of another table. Ultorg will use joins configured in the current perspective to determine the behavior of the dropdown list.

If a field is displayed using the Compact Join feature (see Ultorg 1.0.9 below), its dropdown list will show each of the selected fields from the referenced table.

For fields that are not involved in a join, the dropdown list suggests existing values from the same table column. In either case, any text typed will be used to narrow down the suggestions:

Other Improvements

• Accept semicolons as argument separators in formulas, like in some Excel locales.
• Make new perspective tabs open slightly faster.
• Various minor adjustments and improved error messages.

Version 1.2.1 (2022-09-12)

Data Sources

• MySQL and MariaDB: Significantly improve support for this SQL dialect. In particular, support all date/time types, and provide SQL translations for all of the functions available in Ultorg's spreadsheet-like formula language.

  (Similar work was done for Microsoft SQL Server in Ultorg 1.1.0. Improvements on Oracle and SQLite are still pending. These updates focus on completeness and correctness; the performance of generated SQL queries may be improved later.)

• PostgreSQL: Properly handle enum columns, so that they can be sorted and filtered on (#17). Also handle the special "24:00" value in time and timetz columns.
• SQLite: Fix an "expected a NumberStorageType" error which could occur when doing a SUM or other calculation on columns with certain type declarations (#39).

Data Editing

• Make row insertion work in columns with auto-generated keys (#35).
• When inserting a new row, show explicitly defined values in a stronger shade of green.
  

  (A technical detail:) On some databases, values that are not explicitly defined during an INSERT operation may be assigned a default or auto-generated value. For completeness, the Delete key action has been adjusted to allow the setting and unsetting of an explicitly defined null value.

  See this writeup for more instructions on how to use Ultorg's data editing feature.

Other Improvements

• Add a "Clone Perspective" action in the context menu that appears if you right-click a tab. (During data exploration, it is often useful to make a quick copy of the current perspective.)
  
• Adjust dependency detection when attempting to delete a data source.
• Adjust action behavior in the Folders sidebar.
• Add the keyboard shortcuts Alt+Command+Left/Right for switching tabs, like in Chrome on MacOS. Also add Ctrl+Shift+PgUp/Down for moving tabs.
• Linux: Further isolate the bundled PostgreSQL instance. Fixes a problem on NixOS (#34).

Version 1.2.0 (2022-09-01)

Perspectives, Tabs, and Folders: An Overhaul

This release includes a major redesign of the way Ultorg perspectives (visual database queries) are opened, saved, and organized. This fixes numerous usability issues (#6).

The major changes are as follows:

• Double-clicking a data table () now creates a new perspective, which opens in a new tab. This eliminates the annoying "first create a new perspective" dialog.
• Open perspectives no longer appear in the Folders sidebar unless you explicitly save them. This will make Ultorg's folder hierarchy less cluttered, and more useful for organization.
• Except during data editing, all prompts to "Save Changes?" are now gone. Instead, closed tabs go into a Recently Closed Perspectives folder (), where they can be reopened if desired. You can press Ctrl+Shift+T to reopen closed tabs, like in Chrome. (⇧⌘T on Mac.)
  
• The Save Perspective action ( or Ctrl+S/⌘S) will now prompt for a file name and folder. You can create your own folders to organize perspectives and data sources. Saved perspectives are never changed, unless you invoke Save Perspective again and explicitly overwrite them. The Save Perspective dialog will ask you to confirm before overwriting an existing saved perspective.
  

In addition, the following enhancements will encourage a tidy folder hierarchy:

• Warn if the user tries to add a data source that already exists.
• Prevent deletion of data sources that still have saved perspectives referencing them.
  
• Move perspectives from earlier Ultorg versions (<1.2.0) into a separate folder.

Other adjustments and enhancements:

• New perspectives now show all table columns by default. You can toggle the "Select All" node (introduced in Ultorg 1.1.0, see below) to show a subset only.
• Clipped table layouts () are now the default for all new perspectives. You can change this in the toolbar, e.g. to get a word-wrapped table () like before.
• Apply Data Edits now has shortcut Ctrl+Shift+S, separate from Save Perspective (Ctrl+S).
• Fix a bug that could occur when organizing tables in folders in an external data source.
• Automatically append file extensions in file browser dialogs, on all platforms.

Microsoft SQL Server Connections

• Make Ultorg work with older SQL Server versions. In particular, fix the "COLLATE clause cannot be used on expressions containing a COLLATE clause" error (#9), and provide some alternative formula function translations. Tested on SQL Server 2014 and 2017.
• Various adjustments relating to rounding and numeric precision.

Version 1.1.0 (2022-08-17)

Improved Support for Microsoft SQL Server

• Properly handle date/time types, and UUIDs, in Microsoft's SQL dialect (T-SQL). Table columns of these types can now be properly read and formatted by Ultorg, and values can be edited in Data Editing mode. (This eliminates the "binding not supported" error.)

  New data types supported: date, time, datetime, smalldatetime, datetime2, datetimeoffset, uniqueidentifier

• Properly generate SQL code for the various date/time functions available in Ultorg's formula language. Correctness is tested using Ultorg's existing integration test suite.

  Formula functions translated: year, month, day, hour, minute, second, offsetHrs, date, time, timestamp, weekday, dayOfYear, week, globalUTC, local, localUTC, addDays, addMonths, addYears, diffDays, diffMonths, diffYears, addDayFract, diffDayFract, uuid, weekISO*, weekISOyear*, weekdayISO*

  * Experimental function; not yet listed in online documentation.

Other Improvements

• The field selector now includes a "Select All" node, as a shortcut for toggling field visibilities. Unselecting the Select All node reverts to the default set of visible fields. (You can still select multiple fields by holding down Shift or Ctrl/Command, and use Space to toggle visibilities.)
  
• Improve the heuristics for capitalizing table and column names. Preserve fully capitalized words in mixed-case names, and recognize more prepositions and common abbreviations.
• Fix a bug where the formula bar was not updated after a data edit.
• Make the crosstab button in the toolbar active in more cases.
• Tune Filter checkbox behavior for boolean (true/false) fields.

Version 1.0.9 (2022-07-27)

Time & Timestamp Parsing

Text file sources (TSV/CSV) now support a wide range of date, time, and timestamp formats.

• Ultorg will auto-detect the data type for each column: Date, Time, Time with Offset, Local Timestamp (date+time), Global Timestamp (date+time+offset), Number, UUID, or Text.
• As before, a configuration option exists to disambiguate the day/month order in date formats such as "4.3.2021". Explicit day/month names (e.g. "Mon, January 4, 2010") are recognized in English, French, German, Portuguese, and Spanish. Years require four digits.
• Various separator styles and field orders are accepted (e.g. "16:42:23,123", "2019MAR04", "25 March 2022 at 16:23:52", "Tue, 3 Jun 2008 11:05:30 GMT", "05.06.07 03.04.1955", "3p.m.").
• Various formats for time zone offsets and UTC/GMT/Z specifiers are supported. Non-UTC time zones require an explicit offset (e.g. "12/03/2011 10:15:30 +01 Europe/Paris").
• ISO 8601 formats are always parsed as such (e.g. "2022-07-28T02:12:20Z").
• Unrecognized or out-of-range values (e.g. "30.02.1999", "23:60") are always rejected.

Some improvements were also made to the character set detector and the text import preview.

Data Sources

• Edit Data Source: You can now edit the connection settings of a previously created data source, e.g. to change the host name of a database or the path to a text file. To edit a data source, right-click the data source icon () and click Edit Data Source.
  
• Icons in the Folders sidebar now have tooltips showing, for instance, connection details.
  
• Generated data extracts, which are used to run queries on e.g. CSV files, will be deleted or recreated as appropriate if the user changes or deletes the data source configuration.
• Show an indication of ongoing schema retrieval operations, e.g. when getting the list of tables from a data source (#25). Show a helpful message if no tables are found (#8).
  
• Reword the "Delete" confirmation dialog to mention that external data is not affected.
• Have data sources in the Recycle Bin behave as if deleted.
• PostgreSQL: Fix "Failed to convert string" error when seeing an infinity date or timestamp.
• Google Sheets: Allow the user to paste the authentication link into a browser of their choice. Add a "Sign Out" button to reset permissions. Improve error messages.
• Microsoft Access: Properly load any declared foreign key relationships.

Compact Joins

Whenever your database contains a foreign key relationship with a single column in the key, Ultorg makes a feature known as "compact join" available for the foreign key column. This allows you to show fields from the foreign table as part of the foreign key column's cell formatting, without complicating the output with an extra level of column headers.

This feature has been overhauled to address various usability issues (#4).

• On foreign key fields and their joins, the context menu will show a new "Show Explicit Join"/"Show Compact Join" action, which can be used to toggle between the two modes:

• By default, foreign key fields now show only the key itself. Fields from the foreign table can be selected from the field selector:
  
• Tune the appearance and behavior of the checkbox tree in the field selector, in particular around the "(Show Explicit Join)" node, which can also be used to control compact joins.

Other Improvements

• Make keyboard shortcuts for Undo/Redo work from the Fields/Format/Filter popup.
• Make alphanumeric sorting in the Folders sidebar work more like in Windows Explorer.
• Adjust decimal place detection and the formula bar to work better with currencies.

Version 1.0.8 (2022-05-29)

Quick Count Feature

You can now quickly see the total number of rows returned by your query, in the right-hand side of the toolbar area. This would previously have required a =COUNT style formula.

If there are multiple relations (one-to-many relationships), the row count is done at whichever relation the cursor is currently located.

By default, only the retrieved number of rows is shown (e.g. ">99 rows"). You can toggle the indicator to get full counts. In this mode, Ultorg will run separate COUNT queries in the background, when necessary, as you move the cursor or modify your perspective.

There's a short demo of this feature here.

General Improvements

• Use native file choosers on Windows, e.g. for the Connect to File action.
• Fix numbers showing up as e.g. "99\u00A0999" on locales that use space as thousands grouping character (#21). Avoid showing the NBSP character as an escape code.
• Support proxy server auto-configuration (PAC, used in certain corporate environments).

Data Sources

• Text Files (CSV/TSV):
  • Parse up to 2048 columns, so long as at most 250 are visible as once in a single perspective query. Show informative error messages when limits are exceeded. (#23)
  • Improve character set auto-detection in the presence of Cyrillic letters.
  • Allow all-midnight timestamp columns to be parsed as a simple date column.
• MySQL: Fix "Client restrict authentication plugin" error when connecting (#7).
• MariaDB: Add a workaround for a MariaDB bug to ensure correct formula results.
• PostgreSQL: Fix "cannot use serializable mode in a hot standby" error (#13).
• SQLite:
  • The JDBC driver for SQLite is now bundled with Ultorg.
  • Fix a NullPointerException when working with columns of an undeclared type (#27).
  • Make the TEXT function work properly, including on columns of an undeclared type.
• Oracle:
  • Retrieve the initial list of database tables in a single efficient query. The operation should now be quick even when there are thousands of tables.
  • Properly handle VIEWs with no primary key and where ROWID is not permitted.
• Microsoft Access:
  • Fix a bug in the date/time type detection logic.
  • Avoid opening "linked tables" on paths outside the main database file.

Version 1.0.7 (2022-04-23)

Revamped Text File Connector

Support for connections to delimited text files (comma-separated, tab-separated etc.) has now been completely revamped:

• New UI to configure and preview data import from delimited text files.
• New configuration options to set character encoding, delimiter characters, quoting style, headers, decimal character (comma vs. period), date format etc.
• Date and number parsing now supports a broader range of formats.
• All configuration options have auto-detected defaults. This includes a custom-developed character set detector, tailored for the historical idiosyncracies of CSV/TSV formats.
• An option to "show only rows/columns containing special/non-ASCII characters". This is useful when trying to guess or verify a file's original character encoding.

Visual Query System

• Proper support for the UUID data type (on PostgreSQL only for now).
• Add the fromEpoch formula function (create timestamp from UNIX epoch time).
• Have the "Refresh Table Metadata" action automatically disconnect and reconnect any underlying JDBC connection. This may be necessary after external changes to the database schema and/or access privileges.

Layout System

• If text values contain invisible control characters, show them with grey escape codes.
• Fix a text alignment bug that could happen when printing in landscape mode.

General

• Update libraries and JDBC drivers, and add a vulnerability scan to the release process.
• Various other improvements and bug fixes.

Version 1.0.6 (2022-02-17)

Data Sources

• Remove an unintentional 4KB size limit on cell values in CSV/TSV files.
• For Presto connections, recognize either the Trino or the Athena JDBC driver.
• Avoid creation of multiple extracts for the same table in certain cases.

Application UI

• Various cosmetic improvements in the user interface (borders, alignment etc.).
• Auto-size the Fields/Filter popup, for better positioning near screen edges.

Data Editing

• Allow inserted records to be shown in relations joined on a non-primary key.
• Keep edits visible even when fields within a multi-field key are reordered.
• After applying edits in a perspective over multiple data sources, automatically refresh underlying extracts so that the changes become visible.
• Disallow data editing in extracts, and improve related error messages.

Visual Query System

• Make the function documentation popup visible when starting to edit a formula.
• Some bug fixes relating to the Compact Join feature.
• Some bug fixes relating to columns that are no longer available.

Layout System

• Avoid very wide columns that could occur before fixed-width crosstabs.
• Adjust heuristics for text breaking in labels, and for vertical labels.
• Improve vertical positioning of text within cells.
• Display non-latin text properly on MacOS as well (completes work from 1.0.5).
• Fix uneven letter spacing (kerning) when printing on MacOS.
• Have Ctrl+Home/Command+Up move the cursor to the current table column's label.
• Handle the Backspace keystroke.

Version 1.0.5 (2021-11-19)

General User Interface

• New, polished look for all parts of the user interface outside and around the visual query interface: buttons, popup menus, sidebars, windows etc. The same theme is now used for Windows, MacOS, and Linux, with some customizations to blend in with each platform.
  • On Windows, the main window title bar and menu bar have been collapsed, leaving more space for showing actual data.
  • Lots of smaller adjustments to user interface colors, margins, borders etc.
• Properly display text in non-latin scripts such as Chinese, Japanese, and Arabic, if the relevant base fonts are installed on the operating system. This is useful e.g. when viewing database tables containing a variety of Unicode strings. (Works on Windows and Linux, but not yet in the perspective area on MacOS.)

Visual Query System

• Fix a bug in an interaction between the Compact Join and Custom Group features.

Version 1.0.4 (2021-10-31)

Visual Query System

• Initial support for range filters, i.e. "greater than", "less than", and "between" conditions in the Filter popup. Previously, such conditions required the use of a formula. Range filter conditions are suggested automatically if the user enters one or two values in the Filter popup's "Search" field. This applies to fields with data types such as Number or Date.
• Fix errors that could occur when using the Filter popup, and avoid some non-intuitive behaviors that appeared in the previous release.
• Have Clear Filter behave consistently with Filter on multiple selection.
• Improve the behavior of the Fields popup's "Show Group Hierarchy Only" option when working with schemas that have cycles in their foreign key relationships.
• Avoid showing certain primary key fields as Compact Join fields.
• In the formula bar, avoid showing unnecessary precision for numbers and times.
• Generate SQL queries that may run faster in certain cases, in particular where IS NULL/IS NOT NULL constraints or Custom Group levels are involved.

Data Sources

• Avoid triggering connection attempts from background tasks. The username/password dialog now pops up only after an explicit query action.
• Avoid flashing a perspective warning while loading new table metadata.
• Gracefully retry database queries for certain connection timeout errors.
• Fix a bug where Refresh Extract failed due to metadata not yet being loaded.
• Fix authentication failures for some MySQL connections (+possibly others).
• Fix bugs which could occur if a new primary key appears for an external table.

Layout System

• Fix a bug where the time portion of certain timestamps were hidden by default.
• Fix incorrect column width measurements for certain Compact Join fields.
• Various smaller adjustments to auto-generated layouts.

Version 1.0.3 (2021-08-16)

Platform-Specific Improvements

MacOS

• Add native support for the new "Apple Silicon" (ARM/M1) based computers (select MacBook Air and MacBook Pro models), which were launched in November 2020. This makes queries run faster and the user interface noticably snappier.
• Avoid a spurious "Allow Notifications?" prompt on MacOS.

Linux

• Automatically fix font rendering problems on the KDE desktop environment.
• Better-looking tab components and borders in the user interface.

Windows

• Improvements in the general look of the user interface, including a slightly larger text size.

Visual Query System

• Further improvements to the Filter popup, to allow the user to configure filters without waiting for the complete filter options query to complete. Any value typed into the "Search" box will become immediately available.
• Added the diffMonths, diffYears, and addYears functions to the formula language.
• When Create Extract is invoked on multiple selected perspectives, show an option to create either multiple extracts or a single extract of UNIONed tables.
• Suppress connection dialogs if the user has already clicked Cancel once. (More work on improved connection management is expected in the future.)
• Make Copy (Ctrl+C) on partial query results more informative.
• Fix some minor corner cases (relating to missing tables, and compact joins).
• Tune the bundled PostgreSQL database to improve extract loading performance.

Version 1.0.2 (2021-07-18)

Extracts & Data Sources

• The Refresh button (Ctrl/Command+R or F5) will now automatically refresh any extracts used to evaluate the current query. For file-based data sources (Excel, CSV, or Microsoft Access files), this will only be done if the file has actually changed since the last time the extract was refreshed.

  For now, schema changes (added/removed tables or columns) must still be manually refreshed via the "Refresh Table Metadata" action.

• The Stop button now also stops extract loading operations triggered by the current query.
• Improve the handling of table columns which no longer exist in the external data source, or where the column's data type has changed.
• Avoid spurious connection attempts (+username/password prompts etc.) for data sources not selected or related to the current perspective.
• Adjust rules for data type detection for CSV/Excel/Google Sheets sources.
• Experimental support for Amazon Athena (Presto) connections. Remote queries and most of Ultorg's standardized formula functions are supported.

  To connect to Amazon Athena, follow the instructions on the Download Ultorg page.

• Bulk export: Large database tables, or extracted query results, can now be efficiently exported to CSV files.

  To do this, select one or more database tables or extract tables in the "Folders" sidebar, right-click the selection, and select "Export to CSV". Hold down Ctrl/Command to select multiple items. To export query results, first dump them to an extract using the "Create Extract" action.

  As in previous releases, it is also possible to export to CSV by selecting one or more columns in a query result (Ctrl/Command+A to Select All) and invoking Copy (Ctrl+C), e.g. for pasting into Excel. This will copy only the portion of the query result currently loaded in the perspective tab.

• The Create Extract action now adds missing column labels automatically.

Layout System

• Adjust borders in generated table layouts, for a slightly simplified appearance.
• Improve auto-setting of table column widths for cases with many empty cells, and when a significant amount of extra space is available.
• Allow Print to PDF on MacOS when no physical printers are installed.
• Increase the maximum number of crosstab columns that can be shown in a layout.

Visual Query UI

• When opening the Filter, show a partial list of options immediately, in case the exact filter options query takes some time to run. Avoid interrupting existing queries unnecessarily.
• Add some missing keyboard shortcuts for spreadsheet-like cursor movement on MacOS (Command+Arrow, Ctrl+Home/End, Alt+Arrow/Space, Ctrl+Space) and Windows/Linux (Alt+PgUp/PgDown).
• In the Custom Join dialog, automatically expand relevant data source folders.
• Simplify the Fields/Filter/Format popup.
• Added the INT (parse integer) formula function, on each supported backend.
• Further improve options displayed by the Custom Group action in some cases.
• Make the Unhide action more intuitive when invoked in a Custom Group.
• Fix query UI bugs that could occur after canceled connection attempts.

Application UI

• Improve the appearance of UI tabs and borders on Windows and MacOS. Fix most remaining scaling issues on HiDPI/Retina screens.
• Improve clicking and drag/drop behavior in the Folders and Fields sidebars.
• Reset sidebar/toolbar state on startup. Fix bugs in "Show Editor Toolbar".

Version 1.0.1 (2021-04-01)

Startup and Installation

• On MacOS, Ultorg is now packaged into a proper DMG file, with the customary "Drag icon to Applications folder" window. The application has been signed and notarized with Apple, as is required on recent MacOS versions.
• Fix a problem that caused the application to fail to start on Windows if the containing folder contained certain non-English characters.

Visual Query System

Simplifications

• Get rid of the Collapse Duplicate Rows feature, which used to be shown in the main context menu. It was seldom useful to deviate from the default value, and the option to do so frequently confused users. Equivalent queries can always be constructed using the more flexible Custom Group feature.

  The grouping level options shown by Custom Group have been adjusted slightly, to make certain query changes more straightforward to perform.

• Get rid of the "At Most One Child Per Parent" indication. It was of limited usefulness, took up space, and confused some users.
• Remove the "In Primary Key" menu item. It is easier to see the key in the Fields pane.
• For Custom Group levels, show the "Unhide" label badge at the group level only, as opposed to on every primitive field. This declutters the layout.

Bugfixes

• Properly handle the case where a previously available table disappears from an external data source (one specific bug fixed here).
• Fix a rare assertion failure in the Delete Custom Group action.
• Fix a rare assertion failure when deleting multiple-selected fields.
• Fix tooltips cutting off the last word when showing warnings or formula parameters.

Performance

• Fix certain slow queries with aggregate functions inside multiple Custom Group levels.
• Make the SINGLE formula function more efficient.

Layout System

• Add online documentation for property editors in the Format tab.
• Add an experimental "Strip HTML Tags" option. (Not yet exposed in the Format tab.)
• In Data Editing Mode, show rows pending deletion with a strikethrough style.

Data Sources

• Upgrade the MariaDB/MySQL JDBC driver to the latest version. This fixes problems with primary key detection on MySQL, and adds support for the sha256_password/caching_sha2_password authentication schemes (which are now the default on new MySQL installations).
• Upgrade the Microsoft SQL Server and PostgreSQL JDBC drivers to their latest versions.
• Attempt to fix the authentication page for Google Sheets on certain Linux systems; one user reported the browser window not opening.
• Allow metadata operations for different data sources to happen concurrently. This prevents one non-responsive data source from blocking all others.

Earlier Releases (feature highlights)

Release	Category	Feature
0.9.8	General	Fix startup problems on certain systems
0.9.7	Visual Queries	Adjust retrieval of crosstab columns and filter options
0.9.6	General	Persist all application data across restarts
	Data Sources	Connect to CSV files and Google Sheets workbooks
0.9.5	General	Folders for data sources, tables, and perspectives
	Data Sources	Extracts and extract-based UNIONs
	Visual Queries	Perspectives over multiple data sources
	Visual Queries	Blue arrows to indicate instantiated tables
	Visual Queries	Custom sort orders, and automatic weekday/month sort
0.9.4	Visual Queries	Improvements in formula editor and field selector
0.9.3	Data Editing	Data editing improvements, including Undo/Redo
	Data Sources	Lazy loading of external table metadata
	Layouts	UI for formatting customizations
	Visual Queries	"Contains" filters
	Visual Queries	Adjust and simplify actions in context menu
0.9.2	Layouts	Formatting shortcuts in toolbar
	Visual Queries	Make the Fields tree more usable
0.9.1	Layouts	Print & Print Preview
0.9.0	General	Retina/HiDPI screen support
	Layouts	Compact joins
	Layouts	Robust form and crosstab layouts
	Layouts	Automatic center alignment for certain data
	Visual Queries	Custom Group and Move to Root actions
	Visual Queries	Copy-to-Clipboard
	Visual Queries	Fix most critical bugs and performance issues
	Visual Queries	Improve sorting behavior
	Visual Queries	Enforce distinction between Tables and Perspectives
0.8.2	Data Editing	Basic data editing
	Data Sources	Bundled PostgreSQL database
	Data Sources	Bundled JDBC Drivers
	Layouts	Frozen value headings in form layouts
	Visual Queries	Complete set of documented formula functions
	Visual Queries	Fully robust query evaluator