Broad tables in SQL

[RieksJ]

Earlier (e.g. in #560), we have experienced that whilst tables in MySQL are broad, they are limited in the number of relations that they can hold. This depends a.o. on the type of TGT atoms of such relations. But all that is technical implementation, and should be of no concern to Ampersand engineers.

While developing a project that uses SIAM and four instances of the ButtonCRUD templates. I estimate that SIAM would define some , I ran into this problem again, because SIAM defines some 25+ relations in the SESSION table, and every of the four ButtonCRUD instances added another 16, which turned out to too much for MySQL to handle.

While discussing the design around Accounts, Organizations, Representatives, Groups etc. with @Michiel-s in the Semantic Tree House application, it turned out design decisions (e.g. to NOT make some concept a specialization of another one) are being made for the single purpose that the Concepts should have their own tables.

Issue #999 can be read as increasing the risk, that developers will run into this problem. That is because if we make concepts A and B cyclic/equivalent, their tables are going to be merged, which requires MySQL to handle the union of all their relations in a single table.

This issue therefore asks for a refactoring of the MySQL code generator, the result of which should be that every concept will get its own table.

[hanjoosten]

This has to do with the way things are modeled in Ampersand itself. If you do not like broad tables, you are free to use --sql-bin-tables. Of course, this comes with a performance penalty.

[hanjoosten]

Ampersand knows nothing about which relations will be used frequently together. Even if we would come up with a way to not create large broad tables, it will be hard to decide which relations should be kept together.

[RieksJ]

One way might be to have every Concept have its own broad table. So if you say CLASSIFY A ISA B, then there still would be two tables rather than one. That would have a (slight) performance penalty I guess, but not nearly as much as when forcing developers to use --sql-bin-tables. In order to further reduce performance penalties, we could introduce a switch, e.g. --sql-not-too-broad-tables that would make one table per every concept rather than the current way of doing things.

[hanjoosten]

This is a bad idea. We have been there years ago. The problem with that solution is that a single atom is represented by multipel rows. That will become a headache to control. You might have a look at the documentation of mysql itself. Good chance that there is some way to bypass the limit on the amount of colums in a single table.

[WolframKahl]

Ampersand knows nothing about which relations will be used frequently together.

Ampersand could scan the RULES to see which relations are frequently used together there.

[hanjoosten]

Maybe. Given a set of expressions, what do you recomend?

[RieksJ]

The 'solution' I have adopted in the GRC project is to define new concepts XContext and relations sessionXContext :: SESSION * XContext [UNI,TOT,INJ,SUR]. That leaves me with additional broad tables and the price I pay is that I need to use these relations to 'switch' between them where necessary.

Would it be feasible to define syntax eg XContext EXTENDS SESSION and then treat these concepts as synonyms?

[stefjoosten]

At the moment we have a limit imposed by MariaDB, which allows for at most 1000 columns in one database table. See http://sforsuresh.in/maximum-number-of-columns-per-table-mysql/ for details.
In principle, a workaround for this number should be implemented in the Ampersand compiler to ensure that any Ampersand-script is implemented correctly and efficiently, even if it has over 1000 univalent relations with the same source concept.
Currently, Ampersand suffers from this restriction, which makes the current compiler a partial implementation of Ampersand.

[hanjoosten]

Instead of trying to circumvent limitations of a database, we should see if there are posibilities in using other databases, e.g. tripplestore databases.

[RieksJ]

As a reminder that this issue, while not urgent, is still important, I would like to inform this thread that in a discussion that @Michiel-s and I had this morning, which was about some very fundamental SIAM- and SSI related concepts, we observed that Michiel has made some design decisions for his Semantic Treehouse application that from an Ampersand/conceptual/theoretical perspective are not what you want, but have to be done if the application wants to be practical.