Test that every export format imports back with the same rows #3666

Problem

TablePro exports CSV, JSON, SQL and XLSX and imports all four back (features/import-export.mdx). Each writer and each reader is tested on its own, but no test exports a table and imports the file back to check that nothing changed.

• Export tests check the text a writer produces (apple/Plugins/FormatPluginTests, for example SQLExportInsertModeTests.swift and CSVExportBytesTests.swift). Import tests read files the test wrote by hand (CSVImportPluginTests.swift, JSONImportPluginTests.swift, XLSXImportTests.swift).
• The only export-then-import check is JSON's, in memory (JSONImportPluginTests.swift:148).
• A SQL export can be gzipped to .sql.gz, and import reads .sql.gz. The tests check the part file name and the option (SQLExportInsertModeTests.swift:148, SQLExportOptionsDecodingTests.swift:16), not that a compressed dump decompresses whole.
• The largest export in the tests is about 4 MB, 40 rows of 100 KB (SQLExportStatementSizeTests.swift:97).

A writer and a reader that drift apart (quoting, NULL, line endings, binary, a cut-off gzip stream) pass every test and lose data on a user's real file. It has happened before: a PostgreSQL .sql.gz export that would not import back (#1114, fixed).

Proposed solution

• Hosted tests on SQLite, one per format pair (CSV, JSON, SQL, SQL gzipped, XLSX): create a table with one column of each type SQLite holds plus NULLs, quotes, delimiters, newlines, Unicode and binary values; export it; import the file into an empty table; compare the row count and a checksum of every row.
• A script under apple/scripts/ that runs the same round trip on MySQL and PostgreSQL with a table whose export is over 100 MB, gzip included, and prints the row count and checksum on both sides.
• Cover a split SQL export (dump.part1.sql, dump.part2.sql) restored in order.

Done when every format pair round-trips in the Unit plan, and the script passes on a file over 100 MB for each format.

Alternatives considered

No response

Platform

macOS

Related database type

N/A / General