Skip to content

v0.11.0

Choose a tag to compare

@github-actions github-actions released this 02 Nov 07:49
· 147 commits to master since this release

Highlights

  • SQLite, a fifth database, built in (#179): SQL windows, fetching on demand, Break, explain plan, completion, the object browser and object views with the DDL, maintenance, and results edited in place (#184). Nothing to install.
  • Results edited in place on PostgreSQL, SQL Server and SQLite (#181, #184), as on MySQL, with the rows found by the table's key, which Edit adds to the result.
  • Execute to file on every database (#180), and Save to file… / Load from file… for binary and long text values of a result (#293).
  • Every result of a statement shows, each in a tab of its own, on MySQL, PostgreSQL and SQL Server (#290, #316).
  • SQL Server: Break keeps the connection and the transaction (#384) and stops a fetch at once (#385); a statement run while a result has rows left no longer waits for the rest of it (#383); statements have no 30-second limit (#199).
  • Table DDL that makes the table as it is: on SQL Server temporal, memory-optimized, graph and ledger tables, columnstore, XML, spatial and selective XML indexes, sparse columns, collations, masks, Always Encrypted and statistics; on PostgreSQL owners, grants, comments, storage, UNLOGGED, serial and identity sequences, and every name with its schema.
  • Scripts read the way each database reads them: MySQL's DELIMITER, backslash escapes, # comments and /*!…*/ code, PostgreSQL's dollar quotes and E'…' strings, and a lone ; is no statement (#187–#189, #201–#203, #216, #313).
  • About 180 fixes across the result grid, exports, scripts, completion, the object browser and the DDL of every database.

Added

  • SQLite, experimental (#179): Type: SQLite with the database file (… picks one, New… makes one), built in, so there's nothing to install. SQL windows with results, fetching on demand, Break, explain plan (EXPLAIN QUERY PLAN) and completion; transactions as with Oracle, each window on its own connection to the file. The object browser lists tables, views, indexes and triggers, attached databases as schemas, and object views show columns, indexes, constraints, triggers, dependencies and the DDL. Results of one table can be edited in place (#184), rows found by the primary key, a unique index on NOT NULL columns or the rowid. Scripts end statements at ; and keep a trigger's BEGIN … END whole. Maintenance runs ANALYZE, REINDEX and integrity checks; the scan finds free pages for VACUUM and indexed tables without statistics. A path that doesn't exist fails rather than making an empty database (?create makes one, ?ro opens it read-only).
  • Execute to file on MySQL, PostgreSQL, SQL Server and SQLite (#180): the window fetches the result batch by batch into the file, as on Oracle with the row count while it runs and Break, which removes the incomplete file. INSERT statements are written in the database's SQL.
  • PostgreSQL and SQL Server: results can be edited in place, as on MySQL (#181). Edit adds the table's primary key (or a unique key on NOT NULL columns) to the end of the result, read-only, also when the query doesn't select it. The key is that of the table the query read, found by the search path or the default schema, with quoted names ("My Table", [dbo].[x]) and, on SQL Server, db.dbo.x and db..x; a table of the same name in another schema isn't taken for it. Views and tables without such a key stay read-only and say why. Applying checks the values that were fetched, each type the way it can be compared. The database fills in an identity or serial left empty in a new row. On SQL Server, rows affected are the statement's own also with SET NOCOUNT ON or a trigger that changes other rows.
  • Save to file… writes a binary or long text value of a result to a file (#293), from the cell's context menu or from View value…: the bytes as they are, text as UTF-8. The dialog suggests the column's name with .bin or .txt, or the extension the first bytes say. Load from file…, in the context menu and the value editor of a result being edited, makes a file the cell's pending change: its bytes in a binary column, its text in a text column. Apply binds it whole rather than writing it as a literal; files up to 256 MB load. On Oracle, Apply also binds a value typed that no literal holds.

Changed

  • &name substitution variables are SQL*Plus's, so only Oracle's SQL windows have them unless SET DEFINE ON turns them on (#190). On MySQL, PostgreSQL, SQL Server and SQLite, 'A & B' or a URL with &b=2 asked for a value of amp or b, and changed the text that went to the database. SET DEFINE ON and SET DEFINE OFF are carried out by the window on every database.
  • On SQL Server, ASCII text for a varchar or char column is written as '…' instead of N'…', in INSERT exports and when results are edited (#182): with N'…' a key lookup could scan the index instead of seeking.
  • The misestimate note of an SQL plan says what it means (#177): "⚠ Row estimate off at plan line 35 (INDEX UNIQUE SCAN …): estimated 1 row per start × 318 starts = 318 rows, got none".
  • WHENEVER SQLERROR EXIT ROLLBACK rolls back when a statement fails, as in SQL*Plus (#286), on every database. WHENEVER SQLERROR EXIT without ROLLBACK still stops without ending the transaction.

Fixed

The list below names each fix in a line; CHANGELOG.md says what each one did and does.

Result grid and result editing

  • Numbers sort and add up exactly past a double's digits (#238); the summary keeps small numbers (#278), takes pending changes (#277) and no longer slows every frame (#241).
  • Sorting, filtering and rows fetched later keep the selection and the cell being edited on their rows (#279, #296).
  • Export and Copy all write the rows as shown (#243); copies leave out the key Pythia adds (#249) and values the grid doesn't show (#250); copies quote line breaks and tabs, and Paste reads them (#276); empty texts survive copy and paste (#275).
  • Editing: the right column when the query renames columns (#218); BIT values (#253); 0x… typed is its bytes (#256, #271); a NULL left untouched stays NULL (#262); Tab edits the next cell (#254); Ctrl+Z takes back the last change (#257) and keeps the viewer, the dialog and an edit on their rows (#299, #328).
  • Apply: an Apply that stops part-way keeps what wasn't applied (#247); one rolled back part-way keeps all its changes (#289); in auto-commit a lost connection keeps what was committed (#338, #345); SQL Server connection losses after a statement ran read as such (#310).
  • Binary values held cut short are neither compared nor written (#223); binary values show as binary on every database (#224).
  • Oracle LOBs, LONGs, JSON and VECTOR of fetched rows can be changed (#309, #340); results of Query data and of tables opened with Query can be edited on PostgreSQL (#229, #237).
  • Closing a SQL window or exiting asks about changes not applied (#245).
  • A very long value no longer freezes the grid (#212) or the record view (#220).

Values, files, exports and imports

  • View value… shows images (#231), binary values cut short as binary (#232), keeps up with megabytes (#284) and lets go of images it no longer shows (#333); Edit in dialog… keeps up with megabytes of text (#356).
  • Load from file… puts a file into a cell being edited (#293), into its new row or none (#344), and a load that ends without a file is forgotten (#361).
  • Execute to file writes binary values whole, and exports refuse values cut short (#204).
  • JSON exports: booleans as true and false (#217), every column in the result's order (#297); JSON and Excel keep every digit of a number (#267); Excel keeps fractions of seconds and every database's dates (#192, #193).
  • INSERT exports: SQL Server floats with an exponent and CLR types as bytes (#182); MySQL TIMESTAMPs set the zone they were shown in (#213); Oracle quotes reserved words (#274), names tables as Oracle reads them (#242), writes BLOBs over 2000 bytes as PL/SQL blocks (#314), CLOBs, JSON and VECTOR over 4000 bytes in parts (#317, #342), BINARY_DOUBLE and BINARY_FLOAT as literals of their type (#339), and each literal once (#343).
  • Export tables and Export schema to folder write a file of their own for each object (#280).
  • Import creates a new table as it is inserted into (#335); a dropped data file opens the import only where it can run (#235).

SQL window and scripts

  • MySQL scripts: backslash escapes, # comments, -- only before a space (#187–#189), DELIMITER (#203), /*!…*/ code (#216, #219), SET, SHOW, DESC, EXECUTE and START TRANSACTION end at their ; (#225).
  • PostgreSQL scripts: dollar quotes after a word and E'…' strings (#201, #202); a rule's actions in parentheses keep their ; (#346).
  • A lone ; is no statement (#313); a script runs at the database's pace (#291); the Messages and Output tabs lay out only what shows (#291, #321).
  • A result whose query was closed says so (#312); a failure after a result's last rows is its statement's (#347).
  • Explain Plan runs in the window's session (#269), needs a statement and substitutes as Execute does (#273); AI: Analyze Execution Plan too (#319).
  • The statement history takes in a script's statements once a frame (#318); the Variables panel keeps its width (#197).
  • & is highlighted (#322) and completed (#287) as an operator where nothing substitutes; the formatter reads the text as the window's database does (#259).

Completion

  • A SQL window's names are those of its own session: current schema, search path, USE, attached databases, temporary tables (#320, #362).
  • Completion follows the object browser's changes (#198); offers columns after a table quoted with backticks or brackets (#208), in attached SQLite databases (#236), and in another schema after its objects were listed (#282); closes a name after its opening quote (#234).

Object browser, names and filters

  • Names show with their case (#272, #295, #336); a name with spaces around it shows quoted (#298), an empty one as "" (#281); Drop… names the object as its database writes it (#268).
  • Generated statements and columns are quoted as the database needs (#230); MySQL reserved words are quoted (#233); Compare's sync scripts quote reserved words (#303); schema fields take a quoted name as written (#337).
  • Filters find non-ASCII letters without case on PostgreSQL and SQLite (#264, #292), quoted Oracle names by their lowercase letters (#305), and ß (#306); Search data opens a hit's table and column as they are named (#334).
  • Edit is offered for views, materialized views and SQLite indexes (#300), creates the object where it was (#248), and leaves nothing dropped when the new version fails (#240, #286).
  • A dropped table's view closes, and Refresh says it's gone (#200); folders no longer stay "loading" after a reconnect (#215).

DDL

  • SQL Server tables: columnstore, XML, spatial and selective XML indexes, ORDER and INCLUDE columns (#252, #265, #311); index options and storage (#315); clustered index first, no hypothetical ones (#327); collations (#323), temporal (#324), sparse columns and column sets (#325), memory-optimized (#302), what CREATE TABLE can't give (#326), masks (#348), typed XML (#349), ROWGUIDCOL and NOT FOR REPLICATION (#355), statistics (#357), graph (#360), ledger (#366) and Always Encrypted (#367) tables; Indexes says disabled and hypothetical (#368); none of it needs string_agg (#294).
  • PostgreSQL: serial columns' sequences (#263), owned and identity sequences (#307, #308, #332), materialized views with indexes, comments, grants, owner, data and storage (#301, #330), views' schemas, security options and check option (#329, #351), grants (#331), UNLOGGED, storage and tablespaces (#352), owners (#353), comments (#354), schemas in triggers and routines (#350), foreign keys to another schema's table (#226), indexes' columns and INCLUDE (#228), virtual generated columns on 18 (#227); Drop of an overloaded function (#195).
  • MySQL and MariaDB: a view names other databases, not its own (#285); index prefixes, DESC and expressions (#258, #283); column defaults read as SQL (#260); MariaDB sequences are sequences (#270), TIMESTAMP and INET6 aren't unsigned (#221), routines, triggers and views are replaced in place (#240).
  • SQLite: the ; after a line comment goes on a line of its own (#206).

Sessions and connections

  • SQL Server: Break keeps the connection and its transaction (#384), stops a fetch while rows keep arriving (#385), and a statement run while a result has rows left no longer waits for the rest of it (#383); statements have no time limit (#199); floats keep their exponent (#261); every result of a statement shows (#316).
  • MySQL: every result shows (#290); warnings show (#210); driver errors read as what they say (#222); a statement too large isn't a lost connection (#205); an untrusted certificate or a server without TLS says so (#211); a host's other addresses are tried (#239).
  • Oracle: BINARY_DOUBLE, BINARY_FLOAT and VECTOR values keep their exponent (#304, #341); a program window compiles another schema's unit in that schema (#251).
  • SQLite: blobs by their values (#209); file: URIs make a missing file only with mode=rwc (#266) and share an in-memory database (#364); a file made with New… isn't made again once it's gone (#255).
  • Break never waits for the network (#207).
  • SSH: a wrong key passphrase says so (#194); passwords the keychain doesn't take say so (#214).
  • Several Pythias at once keep each other's connections, reports, history and settings (#196); the connect dialog never saves over another connection (#246).

Other

  • Settings → Keyboard can't take the keys of typing and editing (#191); a tab's ✕ closes only when released on it (#244).
  • Dependencies: tokio 1.54.1 and clipboard-win 5.4.3, the yanked versions replaced (#288).

Full Changelog: v0.10.0...v0.11.0