Dynamically add new tables to existing databases

[FlorianK13]

If new tables are introduced by BNetzA, the current code cannot simply add them to an existing database. For example, when running db.download() and there is already a database initialized, a new table will cause an error. This should be changed, ie. new tables should be added automatically.

They should take the name from orm.py if the new table is already part of a new open-mastr release. Or they should just use the german name otherwise.

[nesnoj]

An automatic creation be great, but the value replacement (e.g. 4=biomass) should be added too.

[FlorianK13]

If only this was already implemented, we would not need to handle #572 under time pressure 🙄

[FlorianK13]

With 0.14.5 we now have at least the case that tables that are new do not result in a crash anymore. However they are still not automatically added.

[nesnoj]

Yes, let's talk about this in our JF

[nesnoj]

Notes from JF:

• Dynamic update needed
• There seems to be no way of getting the structure in machine-readable form prior to the public release. So no early adapted release is possible.
• How to proceed with orm.py?
  • Regularly parse xsd from "Beschreibung des Exports" and create PR with ORM update?
  • Get rid of orm.py - only dynamic creation
  • keep in mind: existing DB structure and CSV export

[nesnoj]

As soon as #635 is solved, we could switch to dynamic creation.
Open question: Are the provided xsd files always in sync with the Gesamtdatenauszug? We could store the xsd set as fallback in the repo though.

[Simon-Will]

Hi, I'm interested in contributing to this project. (Also attended the MaStR4All workshop this week.) I opened #675 and accompanying pull request #676 recently, which would prevent tables from being created automatically. In that pull request, @FlorianK13 briefly mentioned creating tables dynamically, which is probably this issue.

XSD files

Open question: Are the provided xsd files always in sync with the Gesamtdatenauszug?

Hard to answer in a general, future-proof way, but I inspected the current (2025-11-29) MaStR export & XSD files. Using xmllint, I validated each file against its schema and found countless violations. That's annoying, but not a blocker for this issue here since most fall into one of these categories:

• XSD doesn't mark attributes with minOccurs="0" even if some XML entities lack the attributes.
• XSD demands an attribute order slightly different from the one in the XMLs.
• XSD type enums haven't been updated for recently added catalog values (e.g. hydrogen things, flying wind power plants, etc.)

The two most serious ones are:

• Some new date fields have type "date" indicated in the XSDs, but contain datetime values (e.g. 2025-11-01T00:00:00, always with 00:00:00, never an actual time). Most prominently the field "InbetriebnahmedatumAmAktuellenStandort".
  • This could probably be handled by forcing those datetimes to dates, but it's not pretty.
• The EinheitenSolar.xsd contains a new field called "ZusätzlicheMerkmaleDerFlaecheUndDerAktuellenFlaechennutzung " ("ä" instead of "ae" and a trailing space), which is called "ZusaetzlicheMerkmaleDerFlaecheUndDerAktuellenFlaechennutzung" in the XML files.
  • Misspellings of field names mean that we create DB columns that will have only NULL values. And that we must ignore XML fields that don't fit the XSD. But users will then not have access to these values unless the XSD is fixed by BNetzA.

I adapted the XSD files for myself and now the validation passes. Here are the edits that make them pass. I will tell BNetzA about these findings. Let's see what they say.

Conclusion of this little investigation

• The errors I found indicate that these XSD files are updated by hand. This means we must expect them to contain errors.
• Most current errors don't matter much to us: order is irrelevant, allowed enum values aren't necessary, minOccurs would be nice to decide if fields are nullable, but the current orm.py makes everything nullable anyway.

[Simon-Will]

I sent an email to BNetzA about the errors I found above. I suggested they correct them and run a validation themselves every time before adapting the export format. Let’s see what they say.

Regarding the dynamic DB creation and import, here are the scenarios I can imagine right now:

• Fresh download, user wants tables to be created
• Fresh download, user wants to pre-create tables and MaStR should not emit DDL
  • I made the case for this in Give the option of expecting tables to be present instead of creating them #675. It might not seem so relevant for researchers, but it makes life easier for people integrating open-mastr into larger apps or working in restricted environments.
• (Filled) tables exist, user wants to download new data
  • Tables might be up-to-date with current export or not
  • Since existing tables are dropped on the first file, this can be treated as the same case as a fresh download. Or am I overlooking something?
• I’m not familiar with the download via SOAP API. Should it be possible to add data via API to a database that has been created via bulk download?
• Anything else that I’m overlooking?

Assuming the XMLs are valid under the XSDs, it should be possible to create the exact tables we need before reading any of the XML files. (At least for SQLite and PostgreSQL, which don’t demand a max length for VARCHAR fields.)
I think it makes sense to actually generate SQLALchemy classes that we should also be able to print out for users who would like to create their tables after them.
Processing the XML files would then amount to only inserting the data, and the ALTER, DROP and CREATE could be removed from that part of the code.

In the "bring-your-own-tables" use case, it will happen that new export files will have fields or even tables that are not present in the user’s tables. Or fields in the user’s tables will not be present in the export anymore.
I think this can be handled:

• New field: Skip during import. Inform the user about it.
• Deleted field: If nullable in DB, just don’t insert anything into it. If not nullable, it’s difficult; either abort or insert dummy value.
• New table: Skip during import. Inform the user about it.

Invalid XSDs would be hard to deal with in general, but the following measures can protect against most issues:

• Missing catalog values shouldn’t be a problem. We can detect from the type declaration in the XSD (xs:restriction) that it’s a catalog field. Values can just be grabbed from the catalog and inserted as strings. It’s only difficult if we want to use DB enum types.
• Missing minOccurs can be dealt with by just making everything but the primary keys nullable as orm.py already does.
• Quite specific: If a DB column is a date, coerce any datetime to a date before insertion.

Would you like me to draft a pull request for this dynamic table creation?

[FlorianK13]

Hi @Simon-Will - this looks great. Thanks for the preparation effort that you already provide here. This week we will have a dev meeting on 04.12. at 15.30
If you are interested to join, just send me an email: https://www.offis.de/offis/person/florian-kotthoff.html

Otherwise I think we should first discuss a clear idea of how this feature could work before you start implementing things, that could save a lot of work - I'll reply with some thoughts later today in this issue.

[Simon-Will]

Yes, sounds good. Happy to join!

[FlorianK13]

Requirements:

• open-mastr and its data model are often the starting point for following data manipulation. The default behaviour should not change (ie the table names should be as always), so that we do not break the scripts / pipelines from our users
  • This is especially relevant for table names: The decision to use this _basic and _extended names brings many problems, but I think we are bound to it (at least for default behaviour)
  • For column names it is different - in orm.py we mainly copy the existing names. Here we could use an approach where we use the column names from xsd files
  • Still we can add parameters / new methods to deviate from the default behaviour, ie for introducing own database tables etc.
• We need a fallback solution for xsd creation - it can happen at any time that the availability of these xsd files changes, that should not break open-mastr. Some sort of static data model needs to be present in open-mastr (either through orm.py or through own xsd files that are part of the package)

package behaviour

# Origninal open-mastr data model
db = Mastr()
db.download()

# BNetzA Model from xsd files
db = Mastr(data_model = "bnetza")
db.download()

# Own data model, that is somehow provided (sqlalchemy? or dict?)
db = Mastr(data_model = ??)
db.download()

open questions

• Do we keep orm.py?

Remarks:

• The soap API will be deprecated by us Deprecate the download method="API" #622: at least one thing we do not keep in mind is the data model from the soap API 🎉