Saving projects as a zip file (.qetz) with a database inside: a tested prototype and six questions

[ispyisail]

In #865 scorpio810 said he was "very ok" with a zipped .qetz file, as in
Joshua's roadmap note. Before anyone writes C++ for it, I built a
converter outside QElectroTech to check one thing: can every old project
go into the new file and come back without losing anything?

The new file would be a zip holding:

• a small database with the engineering data: folio and project
  information, symbol information, coil–contact links, wires (ends,
  number, colour, section, cable);
• one XML file per folio, saying only where things are drawn;
• the project's symbols, title blocks and pictures as ordinary files
  (pictures no longer base64).

I tried it on 38 projects: the 25 examples, 9 bugtracker
attachments made with QElectroTech 0.4 to 0.100, and 4 GitHub attachments
(the largest has 148 folios). Converted to .qetz and back:

• all 38 came back with the same content as the original: every tag, value and comment, in order;
• today's development version (0.200.1) resaves 37 of them exactly as it
  resaves the original; the 38th it cannot resave the same way twice even
  without the converter (a separate bug I will report on its own);
• 41.3 MB of .qet became 9.3 MB of .qetz.

What it would mean for users: old .qet files keep opening for ever, the
old file is never overwritten, and Save a copy for an older
QElectroTech writes a normal .qet. A .qetz from a newer version is
refused with a clear message instead of opened with data silently missing,
which is what happens to new data in old versions today.

Questions, each with what I would do if nobody minds:

1. Extension: .qetz?
2. Zip library: KArchive, fetched like the KDE add-ons already are?
3. Database in charge of the engineering data, as Joshua's note says, or
   the zip with today's XML still in charge?
4. Format first, then cable tools — so cable data has somewhere an older
   version cannot drop (Where QET stores cable data: a short agreement before more cable tools are built #1288)?
5. Nothing merged before 0.200.2, and one release that reads .qetz
   before any release saves it by default?
6. Anyone with large or old projects willing to run the converter on
   them? It runs on your own computer and sends nothing.

Converter and full results: https://github.com/ispyisail/qelectrotech-source-mirror/tree/qet-container-converter

[ispyisail]

FYI: I'm working towards full database integration, following the QElectroTech code redesign document. This is my bible!

It's a much bigger job than I ever imagined, but AI is learning at a great pace.

Until now, I've been testing against the 25 or so sample drawings. Yesterday, we hit a bug caused by a drawing that contained features not covered by our existing samples. AI has learned from that mistake, and we've taken things a step further.

We've now downloaded every sample file uploaded to the forum and are using them to build a much more comprehensive test bench.

I've spent most of today going back and forth, fixing bugs and preparing for the database upgrade. There's still plenty to do, but we're making progress.

Why, you might ask? In my opinion, we need to get the database sorted first. It's the foundation we'll need to build some really cool features on top of it.

Please use the supplied converter to test your own projects and check for any compatibility issues. Your feedback will help us find problems before the database upgrade goes any further.

[arummler]

FYI: I'm working towards full database integration, following the QElectroTech code redesign document. This is my bible!

This is IMHO still just an (older) idea collection. Don't think you should use it as the final word of god. It does not sound like it was ever intended to be that.

One should evaluate the merit and it requires (based on it) to work out collaboratively a requirements document and architecture. That might take some weeks before anything can be built. Otherwise there is the danger that everything stays in flow and has to be ripped down again. For file formats that's dangerous.

[elevatormind]

Only negative side I can think of is using KArchive, the dependency of KF libraries have been discussed for a long time. See issue #907
An alternative to KArchive needs to be written (or an alternate zip dependency) for the nokde code branch.

I propose using libzip directly (KArchive probably uses it anyway): https://github.com/nih-at/libzip/tree/main
Could possibly be lifted in using CMake FetchContent if system library is not available (win, mac?).

Also, lzma is already in https://github.com/qelectrotech/qelectrotech-source-mirror/tree/master/sources/import/edz/lzma

[scorpio810]

Also, lzma is already in https://github.com/qelectrotech/qelectrotech-source-mirror/tree/master/sources/import/edz/lzma

+1

[arummler]

1. Okay, or could be qetx like openoffice did it.
2. We are in the moment trying to cut down on KDE libs dependencies (QET is not a KDE app) as they are usually not meant to be installed separately. This one would rather introduce another one. KArchiveDirectory looks like but there are a lot of other standalone libraries doing compression/decompression. Even Qt6 has QZipReader/QZipWriter, but only as private API which again poses issues. What about e.g. QuaZip? One just needs to decide whether one needs streaming or unpacks and packs in one go.
3. I am still not onvince about the database. What exactly do you mean with "engineering data". You will have two sources of truth. Both need to kept synchronized or breaks the whole drawing. Any evolution of the data format (e.g. new types) requires more careful database schema design and migration.

Where is the boundary? Is a label engineering data or drawing data? It’s an element info value, but its text item has a position, font and rotation. The same goes for conductor numbers shown on a wire, cable data shown on a sheet, and terminal-strip entries. “Engineering data” needs a definition before it can be stored separately.

Version refusal doesn’t need a DB. “A newer .qetz is refused with a clear message” is a manifest feature: a format version plus a minimum-reader-version field in the zip. That works just as well with XML inside. Allowing older versions to open newer files read-only, or with a warning, may be friendlier than a hard refusal.

Also I am not convinced that one has to get rid of the old format completely. If you look at ODF or OOXML they also have a "flat" version where the binaries are still base64 serialized.

The problem in the moment is that the xml export is all over the place. For me there are three layers:

• Serialization: project to XML parts (project, folios, symbols, …)
• Container: how the parts are packed: legacy single .qet, or .qetz zip
• Storage: where it goes: local file, Git, optional PLM connector (check-in/out, locking, versions, approved parts, all the other options brought up at some point).

Today toXml/fromXml is spread over many classes. A small interface with one serializer per type, registered centrally, lets the legacy .qet and .qetz simply be two writers of the same data.

The binaries are probably the main selling point and elements/titleblocks avoiding xml escape constructions.

What about a structure like this (Claude helped me):
project.qetz
├── mimetype first entry, uncompressed
├── manifest.xml format/min-reader version, folio order, hashes
├── project.xml
├── folios/.xml one per folio
├── elements/…/.elmt project copies of symbols
├── titleblocks/.titleblock
└── media/. pictures, PDFs

XML references the hash (plus original name, type); the hash is checked on load to allow to use two versions in one project. Unused media are removed on save (garbage cleaner). Elements and title blocks become ordinary files which they are already today. Hashes in the manifest let QET detect when a project copy differs from the collection.

The version manifest is used for: a newer file is refused or opened with a warning, instead of data being silently dropped or having to deduced from the presents of certain xml keys which version it could be.

[arummler]

Okay, I tried it with a larger project:
Pro: it seems to be indeedloss less.
Cons:
Every column is TEXT, including booleans (freezeLabel = 'false'). The schema has no types or constraints that model anything. Attributes it doesn’t model go into JSON blobs, including the original XML tag name: {"tag": "elementInformation", "show": "1"}. It even stores where things sat in the XML (info_at, links_at, props_at, ord) so it can put them back in the same order.

Duplication of element_info and element1_label/element2_label/element1_name between xml and sql.

The folio XML no longer says which symbol an element is: there are 0 type= attributes left, because type moved to the database. Its uuid was also replaced by a qc-key. A folio file can’t be drawn, diffed meaningfully, or recovered without the database. They are now totally dependent on each other, info split in two completely different places. Nothing enforces integrity, it if breaks no recovery?

No deduplication of images.

[arummler]

What is the advantage of introducing the SQL? And if one introduces it why keep the folio xmls? And if both are to be kept does it not add too much complexitiy vs what benefit?

Just as a warning: for development and debugging it means that everybody should become familiar with database schemas and SQL queries.

[arummler]

Okay, I missed
"What it would mean for users: old .qet files keep opening for ever, the
old file is never overwritten, and Save a copy for an older
QElectroTech writes a normal .qet. A .qetz from a newer version is
refused with a clear message instead of opened with data silently missing,
which is what happens to new data in old versions today."
so one should just consider a refactor of the storage backend to make it more streamlined. Sorry.

[ispyisail]

On "where is the boundary?": the rule in #1473/#1472 is the database holds what a thing is; the folio file holds where and how it is drawn.

	database	folio file
Symbol	its information (label, comment, …), prefix, links	which symbol it is, position, rotation
Label shown on a folio	the value	the text item: position, font, rotation
Wire	its ends, number, function, voltage, colour, section	its path, the number's text position and font
Folio	title-block fields and custom variables	everything drawn on it
Terminal strip	its terminals, their order and bridges	the strip drawn on a folio

Cables are still only in the project file for now.

[ispyisail]

The format is now written down as a spec, in the source tree next to the code:

• format 3 (Fix .qetz database: rows keyed by position, untyped, unversioned #1473): https://github.com/ispyisail/qelectrotech-source-mirror/blob/fix/qetz-format-ids-types-version/sources/container/QETZ-FORMAT.md
• format 4 (Save as .qetz by default, with the project database as the source of truth #1472): https://github.com/ispyisail/qelectrotech-source-mirror/blob/feat/qetz-db-truth-v2/sources/container/QETZ-FORMAT.md

It covers the files in the zip, the version rules, every table and column, how rows are keyed, and what reading checks. A test fails if its schema stops matching what QElectroTech writes. Corrections welcome.

[ispyisail]

Thanks — agreed on dropping KArchive, given #907.

One thing on the LZMA code: what's in sources/import/edz/lzma is the 7-Zip reader only. It has no encoder and can't read or write zip, so it can't save a .qetz.

I measured the compression methods on the 302 projects attached to the forum (126 MB as .qet), pictures left uncompressed:

Method	Size	Unpack, all 302
Deflate	32.5 MB	0.7 s
Zstandard	30.3 MB	0.4 s
LZMA	28.3 MB	1.6 s

The best saves 13 % over Deflate, and unpacking is about 2 ms per project either way. Deflate is the only one Windows Explorer, macOS Finder and every unzip tool open, and it's what ODF and .docx use — so I'd go with plain ZIP + Deflate, and @arummler's mimetype-first layout.

For the library I'd suggest miniz: one .c and one .h, MIT, reads and writes zip, no zlib needed. It would be bundled like the LZMA code, so packagers and the Windows/macOS/nokde builds have nothing new to provide. QuaZip and libzip both need zlib on top.

[elevatormind]

miniz seems minimalistic and maintained, so probably a good choice for minimal zip functionality

Part of my point about lzma was if lifting in a new compression library for .zip, maybe it could be used instead of the vendored 7-Zip sdk as well for the edz import to have only one common compression library. Or maybe lifting in 7-Zip sdk writer features instead of pulling in a new compression library?

[ispyisail]

Recommendation for the .qetz container:

• Format: plain ZIP, Deflate compression (normal level), as used by .docx, .odt and .jar.
• Pictures (PNG, JPEG) stored without compression — they are already compressed.
• mimetype as the first entry, uncompressed (the ODF convention @arummler suggested).
• Library: miniz, bundled in the source tree like the LZMA code.

Why:

1. Small enough: 126 MB of .qet becomes 32.5 MB. LZMA or Zstandard would save only 7–13 % more.
2. Fast: about 2 ms to unpack a project, against seconds to build its folios.
3. Opens anywhere: Windows Explorer, macOS Finder and every unzip tool read Deflate zips; many do not read LZMA or Zstandard zips. A damaged project can be rescued by hand.
4. No new dependency: miniz is one .c and one .h, MIT licensed, needs no zlib, and fits Remove KF6 framework? #907's move away from KDE libraries. Packagers and the Windows/macOS/nokde builds have nothing new to provide.

[scorpio810]

I think Qetz will only be useful for images or embedded PDFs; users are using more and more images in their projects, and Base64 encoding and decoding on XML .qet really slows down a very large project with a lot of pictures.

I suggest we stick to these two files – ‘pictures’ and ‘PDF import’ – and if everyone’s OK with that, we don’t even need to wait for the next release.

[arummler]

Even just separating out those in v1 of qetz requires at least:

• versioning of the file format as it will actively evolve and to avoid having to add .qetz2 , qetz3, etc.
• directory structure
• naming of files / hash handling

[ispyisail]

AI is 80% though the build and it is massive

Lets see what it comes up with, try it out. Don't like it try something else.

[saschbe]

Hello everyone,

Thank you for all your work on this important evolution of QElectroTech!

I'll be following the progress of this project with great interest, especially regarding the possibilities it could bring to residential electrical installations.

I previously suggested introducing a TAG system for elements, allowing users to classify and search for symbols by function, field of application, or applicable standards (RGIE, IEC, etc.), without changing the existing library structure.

I wonder whether this new data storage architecture could also make such a feature easier to implement in the future.

Thanks again to everyone for your dedication and contributions!

[arummler]

Each element is just a xml file. You can define any time a new xml tag and write your information (tags, unfortunately we have here a duplication of word usage) inside. That's even backwards compatible as by default unknown tags are just ignored.

[ispyisail]

In theory this is the base for everything........

At the moment all data is stored on the XML file (This is how electrial autoCAD works)

It trying to move this to a SQL database (This is how EPLAN works)

Once we have a place to store data the sky is the limit for features

I underestiamed how big this project is im about 2/3 throught the total build. On the plus side it will be transperiant to most users, they will not notice any difference. Backwards compatable is very hard

[arummler]

EPLAN uses a database because it treats a project as engineering data with drawings on top, not as a set of drawings. I'm answering from general knowledge of EPLAN Electric P8, not from anything checked here, so read the version details as approximate.

Why EPLAN works this way. In P8 the device is the central object: its tag, part number, connections, terminals and cables. Pages are views of that data. Reports such as parts lists, terminal and cable plans and wiring lists are database queries, and cross-references are just lookups. Its project files are packed database containers rather than readable XML. Parts management can sit on a shared SQL server, which is what lets a whole company and its purchasing (ERP) systems use one part catalogue.

The advantages:

• One fact, one place. A device's label or part number is stored once, and every page, report and cross-reference reads that one copy. Nothing goes stale. This is exactly the problem 5.5 ran into in QET, where derived text stored in the folio XML went out of date.
• Big projects. Thousands of pages work because nothing needs every page built. Finding all the contacts of K1, or every cable to cabinet +A2, is a query, not a walk through every drawing.
• Reports and checks. Design-rule checks, connection lists and bills of materials are queries over clean tables, not code that scrapes graphics.
• Integration and teamwork. A shared parts database, links to purchasing systems, several people on one project, and partial locking all need a real database.
• Room to grow. New data, such as cable lengths or IEC-style designations, is a new column or table, not a format hack.

The disadvantages:

• Opaque files. You can't read, diff or hand-repair a project in a text editor, and version control (Git) sees binary blobs.
• Corruption and lock-in. A damaged database is harder to rescue than XML, and you depend on the vendor's tools to inspect or migrate it.
• Schema versioning. Every format change needs migrations, plus rules about which versions can open which files.
• Complexity. Contributors must understand a schema as well as the drawing code, which is arummler's worry in Saving projects as a zip file (.qetz) with a database inside: a tested prototype and six questions #1440.
• Fragility on shared drives. Database files and network or cloud folders don't mix well. That's why the plan works on a local copy and writes the zip atomically.

Does the gain outweigh the cost? For EPLAN's market, yes, clearly: industrial customers with large, multi-user projects tied to purchasing systems, who pay for tooling. The database is why reports and cross-references stay correct at scale, and their customers rarely edit project files by hand.

Honestly, I believe the major business reason might be vendor lock-in. That kind of setup requires a dedicated expert IT team to maintain and to my understaning is also bound to windows access rights.
Not sure why anybody goes along with that. You are completely dependen on the vendor on any integration into anything. Instead of a third party PLM (as it is done with mechanical CAD and also SEE) and connectors you put all your egg into one nest. And they still offer a xml format to export to third parties who are not in the same computer network.

If you wanted to do the same (all data in the same db) you would not save individual files but have one database file for all projects on the user's computer.

Considering the varied use cases, and the usual spirit of open source software, it would be good to allow multiple, equally supported, options for everything. Ie saving things in a file, having elements either in a folder as up now, a local database or third parts PLM.

Regarding the versioning: database design is complicated and evolution of functionality like introducing additional objects might require more than "just add a field". I would express worry about the long term backwards compatibility of the file formats considering that multiple parts have to stay in sync.

A shared file on a net drive is anyhow an anti pattern to be avoided. In such a case one has to use a server.

[arummler]

How do you intend to keep across versions both the sql and xml part, as well as the complelely separate "flat" xml export, backwards compatible?

[ispyisail]

Try it out and you will see for yourself

[ispyisail]

forwards and backwards compatable, i spent more token on this that anything else

[ispyisail]

@arummler, a proper answer to "how do you keep the SQL part, the XML part and the flat .qet compatible across versions?":

• One writer. .qet and .qetz are both written from the same project document; the .qetz only splits it into files and a database. The flat .qet can't fall behind.
• Versions. The manifest holds the format and the oldest format that may read the file. A newer file opens read-only if it allows this version; otherwise it is refused with a message. The database holds the same number.
• Rule for future changes: new data goes in a new column or table, so an older reader skips it; a change an older reader would misread raises the oldest-reader number. Every format gets a saved test file that must keep opening (format 2 has one).
• Checked on every change: 342 real projects from QElectroTech 0.3 on come back as the same .qet, and re-save to the same bytes.

[ispyisail]

Yes, by design. XML now holds the drawings, and the database holds the engineering data.

Inside a .qetz (format 3):

• project.sqlite: the engineering data, meaning symbol information, links, wire numbers and properties, folio title blocks and terminal strips, plus QElectroTech's own project database.
• folios/.xml: one per folio, drawing only: positions, wire paths, shapes, free texts, pictures and tables. Since D3c they hold nothing the program can recompute.
• project.xml: the project skeleton, which points to the other parts.
• elements/…: the symbol definitions, still XML like the .elmt files in the collection.
• titleblocks/…: the title block templates, also XML.
• manifest.xml: the format version and the oldest version allowed to read the file.
• images/…: pictures, stored as real files instead of base64 text.

Plain .qet files: still the single XML file they always were. They open forever, and "Save a copy for an older QElectroTech" writes one.

Why keep XML for drawings: it matches Joshua's split (the database for engineering data, the folio XML for where the ink goes). The drawings stay readable and diffable, and old files keep working. Putting the drawings themselves into SQLite is listed under "Avoid" in the plan.

[ispyisail]

Everything on the plan is built. It isn't quite ready to push, and I wouldn't send it as one PR.

What's left before pushing:

• The last ctest run on the full branch, both ways, was still running at my last update. It's the check after the byte-order-mark cleanup.
• Once it passes, these local commits get pushed to your fork:
  • the master merge and its cable fix,
  • wire edits by uuid,
  • D3b step 2,
  • the modal_quit_regression fix,
  • the byte-order-mark cleanup.
• Then the plan and fact sheet get their final numbers.

Earlier runs on almost the same code all passed, including the 342-project gates, but this last one isn't confirmed yet.

Is it a big PR? Yes, very. At the last count (before today's additions) it was:

• about 34 commits over QElectroTech's master,
• about 7,000 lines added in QElectroTech's own source files,
• about 9,400 lines of bundled zip library (miniz),
• about 1,900 lines of tests.

Today's work (wire edits by uuid, D3b, the MCP server support, the test fix) adds more on top, so it's probably 8,000+ lines of QElectroTech's own code now. I haven't recounted. It touches loading, saving, the project database, the scripting interface and the file format all at once.

[ispyisail]

The work is now up as three PRs, overview in #1464:

• Speed up opening big projects: build a folio only when it is needed #1461: folios built only when needed, off by default. No file change.
• Add the zipped project format (.qetz) as a save option #1462: .qetz as a Save As choice; .qet stays the default. The zip library is miniz (one file, MIT), not KArchive.
• Make .qetz the default, with the project database as the source of truth #1463: .qetz by default, with the database in charge. It is stacked on the other two and goes further than pictures and PDFs only, so it is a proposal for the questions here, not a decision.

[ispyisail]

Finished, it was a very big job

3 PR's

#1461: Big projects open faster.
#1462: A new zipped file type you can choose to save as.
#1463 is the third PR, the one that changes things for everyone.

[arummler]

Why are basically all fields TEXT? E.g. element / freezeLabel is a TEXT which writes "false" instead of a BOOLEAN.
There is duplication between key and UUID in the conductor table? Is this intended?
element1_label appears both in the XML as well as in the element_info table?
The user_version is "0" and currently there is no code to handle versions?

[ispyisail]

You were right on these. Fixed in #1473:

• Rows are keyed by the item's own uuid, which stays in the folio XML. No more f1/f2, and no separate key column.
• freezeLabel is stored as 0/1; the "show" flag has its own number column.
• user_version holds the format (now 3). Files saved by current development builds still open.

element1_label duplication: #1472 removes it from the folio XML, since QET recomputes it.

[ispyisail]

The double storage is gone too, in #1472. A .qetz now holds each fact once: project.sqlite has only the project's own tables, and QElectroTech builds its working copy from them when it opens. #1472 also includes #1473 and is now format 4.

Cost: opening is a little slower (136-folio project: 2.2 → 3.5 s; 23 s as a .qet). The file is a third smaller (3.27 → 2.20 MB).

[ispyisail]

Two more from your 10 October comment, in #1473 (and so #1472):

• A folio file keeps each symbol's type, so read alone it still says what is drawn where.
• QElectroTech now checks that the database and the folio files agree when it opens a .qetz: a row no item has, a row on the wrong folio, or information belonging to no symbol. A file that fails is refused with a message saying what is wrong, instead of being read as far as it goes.

Still open: the position columns (info_at, ord…), which the exact round trip relies on.

[ispyisail]

Two more, in #1473 (and #1472):

• The position columns are gone (props_at, info_at, links_at). The folio file already keeps a marker where each block goes back, so they were redundant. ord stays: it's the order of a symbol's information entries.
• A .qetz from a later version whose manifest allows this one now opens read-only with a message, as you suggested, instead of being refused. A file whose min-reader is too new is still refused.

[ispyisail]

The JSON column too: in projects QElectroTech saves, it only ever held the "show" flag of folio and project properties. That flag now has its own number column (#1473), so a saved project's database has no JSON at all — checked on the 25 examples and a 136-folio project. JSON is left only for attributes QElectroTech doesn't write itself (older or hand-edited files).

[ispyisail]

@arummler can you review with the updates

I addessed as many concerns as i could.

A couple very big projects so i have left them for now.

[arummler]

In sources/container/qetcontainerdb.cpp you have specific v2 readers. That's okay but if you have v3, v4... would it not be more matainable to have a staggered upgrade process:
v1 -> v2, v2 ->v3, v3->v4? Instead of needing one reader for each old version knowing how to translate to the newewst data structure?

[arummler]

The QETProject::openFile method in the very (too) big qetproject.cpp contains a bunch of business logic that open the zip file and launch several decoding steps which I would have separated out and encapsulated.

In the same method if I understand it right: in case of a zip file you join the xmls, fill from the database and construct a new xml. That new (authorative) xml is then used in the readProjectXml method to construct the projectDataBase. The projectDataBase is implemented in QtSql while the file format SQL is based on sqlite3. Both have different formats? I guess tha advantage is that as soon as you reach readProjectXml it does not matter anymore whether it was a qet or qetz, both read the same path and one danger of divergence is eliminated. On the other hand the database is never really used for anything. In principle it is converted multiple times back and forth? Writing happens in the opposite order.

For the lazy loading there is a clear danger: if you open only e.g. one page, edit and save: it will potentially save outdated information for the not-loaded pages. There are several signals and a couple of functions which mark pages as stale: e.g.
//What changes the saved items of folios not built (lazy folios):
//a folio's position, number or the folio total in their texts,
//the report and cross-reference settings, a symbol's definition.
//A folio not built is then built to be saved (writesKeptFolios())
connect(this, &QETProject::diagramAdded, this, &QETProject::markKeptFoliosStale);
connect(this, &QETProject::diagramRemoved, this, &QETProject::markKeptFoliosStale);
connect(this, &QETProject::projectDiagramsOrderChanged, this, &QETProject::markKeptFoliosStale);

but these are hand curated lists. If anything is missing it will loose to information loss. I think things like search&replace or re-numbering of elements etc. could be suspicious. Not sure how to catch all the possible changes...

[arummler]

SQLite3 supports binary blobs... I am not sure whether the current approach really makes sense or just increase the complexity. The original idea was to simplify / speed up the XML by separating out binary data and avoid having to merge XML trees together which exist as seaparate files anyhow. That's simple, readable, diffable. But the internal QtSQL has to be constructed from that.

Your idea of using primarily a data base has its merits but also disadvantages. But if one wants to go the route then why not
https://www.sqlite.org/appfileformat.html
SQL as a file format with a real schema, migrations, constraints and on-demand loading through queries? It could be even be the project data base but working on a copy. And the XML+zip only as a stream out / stream in of the project data base.

Otherwise everything seems to be mixed up in the moment.

[arummler]

What the SQL as file format would potentially do automatically:

• crash safety
• loading on demand
• save incrementally
• takes care of the binaries

[ispyisail]

@arummler Thanks. I checked all five against master, and you read the code right.

• Upgrading one format at a time: agreed. I'm changing the reader to do v2 → v3 → v4 in steps.
• Folios not built yet: agreed, this is the real risk. The lists are kept by hand, and our tests only open and save, never edit. I'm adding a test that makes each edit twice, once with lazy folios and once with every folio built, then compares the saved files.
• Engineering data: you're right, autonumbering is split three ways. I'll post a table showing where each kind of data lives now and where it should go.
• openFile(): I'll move the container reading into its own class.
• SQLite as the file itself: an SQLite file can't be saved bit by bit or survive a crash while it sits inside a zip, so it's one or the other. @scorpio810, which way do you want it?

[scorpio810]

https://www.sqlite.org/howtocorrupt.html
https://www.sqlite.org/security.html
https://www.reddit.com/r/sqlite/comments/1es9xih/sqlite_corruption/

[arummler]

Basically Sqlite is not foreseen to be put on a network drive and/or have shared access. I still think that this is a separate feature from a local user file format. But it seems the recommended way is either a) to build a server application or b) to switch to PostgreSQL. The latter has all these features for safe concurrent access (with thousands of users, highly granular) already built in.

[arummler]

Point 1.) under recommendations:
https://www.sqlite.org/useovernet.html