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.
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:
- 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)
- 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).
- 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.
- 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. This 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 pane 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. (Command+Shift+T on Mac.)
- The Save Perspective action ( or Ctrl+S/Command+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.
- Automatically gather 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 using folders to organize tables 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.
- 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 table 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". Spelled-out 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.
- 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.
- Hovering over icons in the Folders pane will now show a tooltip with e.g. 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 make it clear 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 date/timestamp column contains infinity.
- 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, like in other databases.
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.
- Make keyboard shortcuts for Undo/Redo work from the Fields/Format/Filter popup.
- Make alphanumeric sorting in the Folders pane 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 grouping levels (one-to-many relationships), the row count is done at whichever grouping level 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.
- 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). In general, avoid showing the NBSP character as an escape code.
- Support proxy server auto-configuration (PAC, used in certain corporate environments).
- 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 which could cause incorrect formula results.
- PostgreSQL: Fix "cannot use serializable mode in a hot standby" error (#13).
- 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.
- 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.
- If text values contain invisible control characters, indicate them with greyed-out escape codes.
- Fix a text alignment bug that could happen when printing in landscape mode.
- Update libraries and bundled JDBC drivers, and add a vulnerability scan to the release process.
- Various other improvements and bug fixes.
Version 1.0.6 (2022-02-17)
- 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.
- Various cosmetic improvements in the user interface (borders, alignment etc.).
- Auto-size the Fields/Filter popup, for better positioning near screen edges.
- 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.
- 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 platform-specific customizations to to blend in with each native platform.
- The new look should appear automatically, but can also be explicitly selected by opening the Internals→Platform Options dialog and selecting Appearance→Look and Feel→"FlatLaf Light".
(Might be necessary if experimental settings were changed in the past.)
- 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.
- The new look should appear automatically, but can also be explicitly selected by opening the Internals→Platform Options dialog and selecting Appearance→Look and Feel→"FlatLaf Light".
- 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 feature and the Custom Group action.
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.
External 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.
Automatic Layout Generator
- 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)
- 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.
- Automatically fix font rendering problems which occurred on Linux when using the KDE desktop environment.
- Better-looking tab components and borders in the user interface.
- 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 will now also stop any 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. (More work will be done in this area in the future.)
- 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, first download the Athena JDBC driver and add it in Ultorg via Internals→Services→Databases→Drivers→New Driver.
- 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.
- 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 toolbox, show a partial list of options immediately, in case the exact filter options query takes some time to execute. Avoid interrupting ongoing perspective 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.
- 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
- Get rid of the Collapse Duplicate Rows feature, which used to be shown in the main context menu. It was seldom useful to change the defaults for this setting, 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.
- Get rid of the "In Primary Key" context menu item. It is easier to see the primary key in the field selector.
- 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.
- 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.
- Fix certain slow queries involving aggregate functions inside multiple custom grouping levels.
- Make the SINGLE formula function more efficient.
- Add online documentation for property editors in the Format tab.
- Add an experimental "Strip HTML Tags" formatting option. (Not yet exposed in the Format tab.)
- In Data Editing Mode, show rows pending deletion with a strikethrough style.
- 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)
|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||Persistence of 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 support|
|0.9.0||General||Retina/HiDPI screen support|
|Layouts||Robust form and crosstab layouts|
|Layouts||Automatic center alignment for certain data|
|Visual Queries||Custom Group and Move to Root actions|
|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|