Rethinking .qetz: each piece of project data kept in one place

[ispyisail]

@arummler, your comments on #1440 were right, and I'm rethinking .qetz because of them. In the format now in master (format 4) the project data sits in a database inside the zip, but QElectroTech still works on XML: opening turns the database back into XML, QElectroTech then builds its own database from that XML, and saving does the reverse. The same data exists in three forms, with converters between them. Below is a new design and the order I would build it in. None of it is built yet; I'd like your review first.

What goes wrong today. A folio that is not open is saved from its old file, so the save is right only if the edit that made it out of date is on a hand-kept list — your point about those lists. On 342 real projects (25 kinds of edit, each saved with folios opened lazily and with every folio drawn), one edit lost data in one project: removing the folio holding the start of a table split over several folios left the next part without its query. A test that does this for every change is coming as a PR.

The new design

• The project database is the only home for project data: symbol information, links, wires, numbering, folio title blocks, terminal strips. QElectroTech reads it while it runs, so nothing is held twice. (A placed symbol's information already comes from it in master.)
• A folio file holds only the drawing: which symbol is where, wire paths, shapes, where each text sits. Nothing another folio can change.
• Anything QElectroTech can work out — labels from formulas, report and cross-reference texts, folio numbers, table rows — is not stored. It is worked out when a folio is drawn.
• Opening reads project.sqlite straight into QElectroTech's database, with no XML step, so search, the parts list and cross-references cover the whole project at once. A folio is drawn when you open it.
• Saving writes the database, writes the folios that changed and copies the others. Since a folio file holds nothing another folio can change, copying is always right and the hand-kept lists go.
• XML conversion stays only at the edges: opening an old .qet, and Save a copy for an older QElectroTech.

Why a database rather than XML in charge: I want to import projects from Promis.e and RSWire, which keep their devices, wires and terminals in database tables, and people ask for SQL reports on project data. Both need one documented place where each fact lives.

What it keeps that you didn't like: a folio file read on its own shows the drawing but not the labels and information, and a damaged database loses the data of the whole project. Previous versions kept on every save (#1501) and recovery (#1504) are the answer to that, not the format.

Order of work. Nothing changes on screen at any step.

1. Hold the file at format 4: no format 5 until step 4 (draft Add .qetz format 5: all numbering in the project database #1510 stays a draft).
2. One home for each fact while QElectroTech runs: links, numbering, the rest of the title-block and wire data; labels and report texts worked out, not stored. No file change: every save stays byte for byte the same, which is the test.
3. Open and save the database directly, in a class of its own instead of inside openFile(). This removes the XML round trip from every open and save.
4. One format change, at the end: format 5, with keys and constraints, numbering in the database, nothing computed stored, and folio files holding the drawing only. Format 4 files are upgraded when opened (Read older .qetz files by upgrading them one format at a time #1496).

Questions

1. @arummler: does this answer the two-sources-of-truth and hand-kept-list problems? What should a folio file keep so it can still be read on its own?
2. @arummler: you suggested SQLite as the file itself. Here the database sits in the zip next to the drawings, symbols and pictures, and QElectroTech works on its copy in memory. Close enough, or do you see a reason to go further?
3. @scorpio810: is the database as the one home for project data still the direction you want, as in Joshua's roadmap? If not, I'd build the other way instead: the zip with XML in charge and the database only in memory.

[ispyisail]

"SQLite as the file" Im also starting to like this idea

a single .sqlite: data as tables; each folio's drawing, the symbols, the title blocks and the pictures as rows in it; no zip

one file any SQL tool opens; saves write only what changed; SQLite itself makes saves crash-safe

[ispyisail]

im rethinking the whole qetz project
There are some issues (sync)

I've been patching bugs to fix issues but the fundamenation question remains "Who is the sole source of truth"

qet = XML (100%) source of truth
qetz = SQLite (60%) + XML (40%)

On top of that these is conversion issues

XML >> SQLite >> XML (single page) >> SQLite etc etc

In my opioion we either need to stick 100% XML or go 100% SQLite (or database)

I don't think we could ever become a professional ECAD software if we stick with XML (or the way were were using it)

Somebody will just rip an open souce copy of EPLAN

Thoughts?

@arummler @scorpio810

[ispyisail]

@scorpio810 @arummler, following up my two comments above: before writing any C++ I built a prototype of the "100% SQLite" idea and tested it on 342 real projects. The project is one SQLite file, with no zip and no XML inside. XML is made only for copy/paste, export and older versions. Every project converted and came back identical. On a 136-folio project it opened in 0.03 s and saved in 0.15 s (today 5–30 s). Hundreds of simulated crashes, power cuts and copies taken mid-save never damaged the file. A record of who changed what costs about 2 KB a save, and two people's copies can be merged.

Four questions decide whether I go on:

1. One SQLite file instead of the zip?
2. The database as the only source of truth?
3. QElectroTech ships its own SQLite (needed to rescue damaged files)?
4. History kept inside the file?

Nothing a released QElectroTech saves changes until you agree.

[arummler]

I would says the central issue is that development builds are being used for production while we are targeting to re-write core parts of QET. We’re discussing changes as large as replacing the file format and the internal data model. I tried to start a discussion about the overall requirements and the technical design first while features and formats kept landing in master at the same time. Changing the internal data model could make sense as looking into the available standards (IEC discussion, I browsed quickly some of them and asked Claude to summarize key points, also taking the hydraulics ones into account) showing different "views" would definitely make sense.

I’d distinguish two kinds of breaking change:

• Changes in functionality or UX. Users have to learn a different workflow, or find a function in a different menu. Annoying, but recoverable: it can be adjusted in the next version, and nobody loses work.
• Changes that affect existing project files or hand-made data: projects, elements, cables, title blocks, anything that took someone’s work and time. They can make data unreadable, lose part of it, or force rework (for example adding a different view to every element). Once released, they can’t be undone. Every format that ships has to stay readable forever (or should offer at least an upgrad path or has to define a breakage). And I am definitely only targeting to bring data forward. Any reading of newer data with older QET versions I would consider out of scope due to difficulty and I would actively hinder that.

The second kind is the critical one. It either needs an agreed design before code lands in master, a tested migration path, and no default switch on development builds until the format is frozen. Or if we do big changes in master (file format, interal reprentation, some idead coming from the different IEC diagram types) we need to consider master purely experimental until tagging.

That’s also why I’ve been pushing back a bit against small “bolted-on” requests, like the current one touching potentials. Each new flag or attribute in the file looks harmless on its own. But once it’s in a released format, it’s there forever: either we carry it along indefinitely, with business logic that has to respect it everywhere, or we break file compatibility later, ideally again with a migration (otherwise people are not happy). If a more comprehensive solution is coming with the rework anyway, every bolted-on field in between is a future migration case. Better to collect these requirements and solve them in the redesign.

On “we can’t become professional ECAD with XML”: I don’t think the goal should be defined as copy EPLAN 1:1. Other professional tools in the same or neighbouring domains, commercial and open source, made different decisions. KiCad stores projects as text files. Altium uses file-based projects, with central managed storage as an optional addition. As far as I understand, EPLAN offers an integrated solution where one central database covers several problems and effectively runs a whole company’s engineering. I’m not against offering that as an option in QET. But it shouldn’t be mandatory: there are other private and professional workflows and environments, from a single user with a local file to companies with their own PLM system(s) including various middleware. Another aspect is that e.g. the fluidics would add a completely different domain which is very similar but requires a few changes to make it universal.

What about this "philosophy":
Where and how projects are saved should be configurable: local file, central database, third-party PLM.
The same goes for input data: elements, supplier databases, cables and so on.
The core of it all is the internal data model, with one place where it’s converted to and from any of these backends, with proper versioning. A database, an XML file (flat or zipped) or a PLM connector are then backends behind that interface, not the model itself.

“Database as the only source of truth while QET runs” means the drawing becomes a view of the database. That’s a rewrite of QET’s core. I makes sense but it has to be decided. As you wrote all the converting back and forth makes it increasingly hard to keep it stable and allow major re-writes.

On the four questions: they are implementation choices and depend on requirements we have to agree on:

What must a project file guarantee: readability, recovery, diff and merge, long-term archiving (containing all the parts), history?
What must the data model support: import from Promis.e and RSWire, SQL reports, PLM connectors?
Which of those are interfaces (import, export, backend) and which really require changes to the core to allow support?
etc.

1. One SQLite file instead of the zip?
   I would still support different formats as they serve different purposes: cooperative work, single user, exchange (by e-mail), archiving, etc. We should just clearly define the goal for this specific file format: e.g. self contained or shared work. This does not mean that the structure should be different between internal, self-contained, shared database. On the contrary if feasible they should have the same schema to make it simpler. But e.g. for the shared use case SQLite3 might not be the right choice as the write themselves on their web site.
2. The database as the only source of truth?
   Again I would differentiate: a self contained project should be its own truth. Any update from one or multiple data bases, elements directories whatever should be user triggered. Regarding the self contained project containing a mixture of XML and SQL and binaries: in the moment I still have the impression that rather increases the complexity. So yes, SQL as the only source. But I would try it first to see how it works. And we really have to define the relationship between internal state and any external representation.
3. QElectroTech ships its own SQLite (needed to rescue damaged files)?
   I would just avoid to use unnecessarily multiple libs e.g. QtSQL and our own. If we think we need it we include it. But that's not a hard statement. If there is reason to include multiple we can decide in that direction. Just consciously.
4. History kept inside the file?
   The file grows with every save, and names and change history travel with every shared copy. Users with Git on top of their file system or a PLM system already have history there. I’d make it opt-in at most. And the save points need to be defined.
   Regarding "two people's copies can be merged.": whether that's git on top of XML, two SQL data base files or something different, it is a hard problem usually. As far as I know most CAD programs try to avoid that due to the complexitity. Just trying to imagine scenarios: Something like adding a shape to a page is simple, okay. But imagine changing an auto numeration formula, re-shuffling pages, editing an element....

[ispyisail]

@arummler thanks, this helps. My answers first, then the requirements you asked for.

Process: agreed. The new format stays behind an experimental setting, off by default, until it is frozen and you both agree. Nothing for it is added to today's files.

1. Several formats on one schema: agreed. The SQLite file is for one person at a time. Sharing goes through a lock (Warning when someone else already has a project open #1489), or later a server, not SQLite on a network drive.
2. The database is the model, not one backend among several: the drawing is a view of it. XML, the zip and a PLM connector become import and export from that schema. That is the core rewrite you describe, and I'd like it decided as one.
3. Only our own SQLite for project files, not Qt's beside it, so one library.
4. History off by default, opt-in per project. Merging and release marks too.

Older versions: an older QElectroTech refuses a file it cannot read. One it can read keeps the parts it does not know, so newer data is never silently dropped. I'd keep that; it costs little.

Merging: agreed, it is hard. Only changes to different things merge. A changed formula, a new page order or an edited symbol is shown as a conflict for the user to settle, never resolved automatically.

What a project file must do:

• open and save with nothing lost; every existing .qet and .qetz keeps opening
• never be damaged by a crash, a power cut or a copy taken during a save; the last saved version is always there
• detect damage when opened, and rescue what it can
• hold everything it needs (pictures, symbols), readable without QElectroTech (it is plain SQLite)
• optional: history, merging

Imports (Promis.e, RSWire), SQL reports and PLM connectors are interfaces on top of the model, not changes to its core.

The prototype was the "try it first": every point above except rescue is tested on 342 projects. What is missing from this list?

[scorpio810]

One requirement is missing from the list: a project must remain usable
in a version control system like git.

This is already a problem with today's .qetz, not only with a future
single SQLite file. A .qetz is a compressed zip: for git it is a binary
file, so there is no readable git diff, no merge, and almost the whole
file is stored again at each commit (the compression changes the bytes
as soon as anything changes). Inside, the folios, symbols and title
blocks are XML that git would handle well, but the engineering data
(element informations, links, terminals...) is in project.sqlite, which
git can't compare even unzipped.

Today the only format that works well with git is the plain XML .qet:
users can see what changed between two revisions (a label, a folio, a
terminal), review it in a merge request, and git only stores the
differences.

So I'd like both formats to be kept:

• .qetz (and whatever comes after it) for speed, size, embedded
  pictures and crash-safe saves;
• .qet as a complete, first-class save format, not only "save a copy
  for an older QElectroTech": everything a project contains must be
  saved in it, with a stable output (stable order of elements and
  attributes, no ids or timestamps changing at each save), so that
  saving an unchanged project gives an identical file and a small
  change gives a small diff.

Both should share the same schema, in line with arummler's "several
formats, one schema", and the user chooses the format that fits their
workflow.

[ispyisail]

@scorpio810 agreed, I've added it: .qet stays a complete save format, chosen per project, with stable output, on the same schema as the binary file. Only the change history and the repair data stay in the binary file; git covers those for .qet.

I measured today's .qet against your rules on the corpus:

• order of elements and attributes: stable (341 projects came out byte-identical across runs);
• a small edit: typically 1–3 changed lines;
• an unchanged project: not identical. Every save rewrites saveddate, savedtime and savedfilepath (date in the computer's locale, full path);
• deleting a symbol gives the remaining wires new random ids each time (92 cases in 80 projects).

I'd fix the last two first, as small PRs on today's QElectroTech: the saved date, time and path change only when the project changed, and a wire's id after a deletion comes from its two ends. Plus a test that an unchanged project saved at another time, in another language and from another folder gives an identical file. OK?

[ispyisail]

BTW i have a new format comming called .qetdb

It has everything @arummler and @scorpio810 have requested plus exta cool stuff like Reed–Solomon error correction

[ispyisail]

@scorpio810 @arummler, I've gathered everything you've both asked for on the file format, here, on #1440 and in the #1472 review (35 points), with our own requirements, into one plan. Here it is, with the questions only you can answer at the end.

The direction. A project is one model: the project database. It can be saved as three files, all written from that same model:

• .qet: one XML file, complete, with stable output, the file for git;
• .qetz: today's default, pictures inside;
• .qetdb: one SQLite file, fast for big projects, repaired after disk damage, history optional.

All three stay for now. In the long run I'd like to end with one file, the .qetdb: drop .qetz once .qetdb has proven itself, and perhaps .qet later, if a readable git diff of the .qetdb is good enough. Nothing is dropped until you agree, and every older file keeps opening for ever.

What every project file must do

• nothing lost on save; every older file opens
• never damaged by a crash, a power cut or a copy taken mid-save
• damage detected on opening; repaired within a limit, rescued beyond it
• a hostile file cannot crash QElectroTech or run anything
• every object has a lasting id, never its place in the file
• a format version and oldest-reader number; upgrades one step at a time
• usable in git: the same project saves to the same file, a small change gives a small diff
• pictures once each, by hash; self-contained; readable without QElectroTech
• one person at a time (the lock); never live on a network drive
• typed data, each fact stored once
• history and merging off unless chosen

Most of this already holds for .qetz. For .qet, two saves of the same project still differ. Two small fixes are open: #1520 (the saved date lagged one save) and #1521 (a deletion gave a wire a random id).

Order of work, one PR at a time against master, nothing stacked:

1. stable .qet and .qetz saves (Fix the saved date and time lagging one save in the title block #1520, Fix a deleted symbol's joining wire getting a random uuid on every run #1521);
2. the program reads and writes the database directly: links, numbering, title block and wire data; the zip code out of openFile(); no hand-kept lists of what makes a folio stale;
3. one serializer per kind of object, registered in one place (arummler's layers);
4. a working copy, so nothing is lost in a crash;
5. .qetdb behind an experimental setting;
6. crash, power-cut, damage and hostile-file tests in CI;
7. history, releases and merging, all optional;
8. defaults: only when you decide.

No default changes on development builds before a format is frozen, and no new fields in today's files meanwhile.

Questions

1. The database as the model, the drawing a view of it: do we decide that as the core rewrite it is?
2. Can Save keep a .qet as a .qet (chosen per project), instead of writing a .qetz beside it? Git users need that now.
3. Should QElectroTech ship its own SQLite for project files, in place of Qt's, so there is one library? Rescuing a damaged file needs it.
4. Should history live inside the file at all (opt-in), or be left to git?
5. Older versions: keep the parts of a newer file they don't know, or refuse newer files?
6. Lazy folios are on by default in master. Should they go back off until the next release?
7. One file in the end, the .qetdb: do you agree with that as a direction, with all three kept until it has proven itself?
8. Is anything missing from the list above?

[scorpio810]

@ispyisail thanks for putting everything in one place, this plan is much clearer. Moving to one PR at a time, with nothing stacked and no default changes on development builds before a format is frozen, is exactly what we needed. @arummler thanks for pushing for this.

My general position: three formats, user's choice

My philosophy for QElectroTech has always been to let users choose what suits them best, rather than deciding for them. So my target is not "one file in the end". It is three formats written from the same model, all equal in content, and the user picks:

• .qet: one readable XML file, for git, long-term archiving, and readability without QElectroTech;
• .qetz: self-contained, for exchange by e-mail and everyday use;
• .qetdb: for large projects and SQL reports/integration.

None of them is a second-class export. No format is scheduled for removal: one goes only if nobody uses it any more, not because another one is considered better.

Answers to your questions

1. Database as the model, drawing as a view of it. This is the core rewrite and has to be decided together with @joshua. I can accept it as a direction on one hard condition: a lossless round trip .qet → model → .qet (and between all three formats), tested in CI on your 342-project corpus. Without that test, "three equal formats" doesn't hold.

2. Save keeps a .qet as a .qet. Yes. Save always keeps the format the project was opened in; only Save As changes it. A default format for new projects can be a preference. If master currently writes a .qetz next to an opened .qet, please treat it as a regression to fix now, independent of the rest of the plan.

3. Shipping our own SQLite. Yes, we already bundle pugixml and SingleApplication, and it makes CI simpler: every runner builds the same SQLite version with the same options, nothing to install. It matters most on macOS, where every extra dependency in the Homebrew/MacPorts build environment (Qt6 + KF6 + SQLite) is a pain to build, sign and notarize. If we do it, let's use only our SQLite for project files and drop QtSql there, so the bundle doesn't carry two SQLite copies (Qt's plugin bundles its own).
   But SQLite gets regular security fixes and parses files that come from outside, so please keep a CMake option to build against the system libsqlite3 (bundled stays the default), plus one Linux CI job using it, for distribution packagers. That means QElectroTech must not depend on compile-time features that distributions don't enable. Which ones would you need (recovery API, session extension)?

4. History inside the file. My preference is still to leave it to git with .qet. But before deciding, how do you plan to implement it in .qetdb: a change-log table, or the SQLite session extension (changesets)? If it's the session extension, is that the real reason for shipping our own SQLite, since distributions may not build it in? And:

   • Schema upgrades: how does history recorded in format 5 stay readable and restorable after an upgrade to format 6? Is the whole history migrated too?
   • Deleted data: content removed from a project stays in its history. We'd need a "save a clean copy without history" before sending a project to a customer.
   • Identity: which name is recorded, and is it OK that it travels in every shared copy?
   • Restore: what can the user actually do with it (whole project, one folio, compare two versions), and with what UI?
   • Size: the 2 KB per save, measured on which edits? A full renumbering or folio reordering produces much bigger changesets, so we need a pruning policy.

   In any case: opt-in, off by default, and it should not drive the other design choices.

5. Older versions reading newer files. Refuse with a clear message when the file's oldest-reader number is above the running version. Keeping unknown parts while the known parts get edited risks inconsistent data.

6. One file in the end, the .qetdb. No, see above: three formats, user's choice.

7. What's missing:

   • Network drives. "Never live on a network drive" doesn't match reality: in companies, projects live on SMB shares. .qet and .qetz must stay safe there, and .qetdb should detect it and warn the user.
   • A tested migration path for the format-4 .qetz files already produced by development builds.
   • Packaging: MIME type and file association for .qetdb, plus Flatpak, Snap, macOS and Windows installers.
   • A documented, published schema.
   • The prototype published, so the performance figures can be checked. I'd also like a comparison with a well-done .qetz format 5 (single model, no XML round trip, atomic save) to see the real gap.
   • Where this sits relative to the 0.200.x stable releases.

[arummler]

• Network drives. "Never live on a network drive" doesn't match reality: in companies, projects live on SMB shares. .qet and .qetz must stay safe there, and .qetdb should detect it and warn the user.

Again, I would differentiate between single and multi user access. The SQLite3 manual clearly recommends not to do although there are a few workarounds offered for single user purposes. For stable network drive operation / multiple synchronous users they recommend to switch to PostgreSQL (so a server based database).

[ispyisail]

Do you want me to switch to PostgreSQL for .qetdb?

My thoughts were stick with SQLite for now then move sometime in the future to PostgreSQL or what ever

Would increase the dependances, program size or installation complexity

[arummler]

No, I agree, that's too much at once I guess. Better to get first internal database <--> exported database ready with a stable XML import/export without introducing additional ccomplexity.

I just mentioned it to avoid trying any workarounds to make an SQLite based database file work properly for network drives or even collaborative work. It should be clearly stated to users that this file is only for local usage. Any extension to a shared project (database) file or shared database would then require a different solution. And that would likely involve installing a proper database server or some kind of QET server (depending on the exact goals).

[arummler]

What is qetz supposed to be now? The original idea of minimum invasive binary separation? Or the current mix of SQL/XML/binaries in version 4/5? Are we now committed to keep in indefinitely?
I’d suggest .qetz = the .qet XML split into parts and zipped, pictures as files, no database inside. Then .qet and .qetz share one serializer, and format 4 files are upgraded once on opening.

Lazy folio loading I would deactivate until the whole internal database question and stable file formats are sorted out.

CI: absolutely, a round trip as well as a save could support (even if not proof) that everything stays stable. But one should use some test file which uses all the (new) features.

[ispyisail]

Im not happy with .qetz now either but its deeply imbeded and in using it as a base for .qetdb (its really any extension of .qetz)

[ispyisail]

Closing: this has been overtaken. The pieces merged (#1472, #1496, #1501, #1504) and the format work continues in #1648.