https://homeproj.cesnet.cz/https://homeproj.cesnet.cz/httpauth-login/favicon.ico?16194486082019-02-01T10:11:29ZHomeproj: Redmine for CESNETMentat - Support #4628: Consider/implement modeling of event database with SQLAlchemy declarative base.https://homeproj.cesnet.cz/issues/4628?journal_id=166652019-02-01T10:11:29ZJan Machjan.mach@cesnet.cz
<ul><li><strong>Related to</strong> <i><a class="issue tracker-2 status-5 priority-4 priority-default closed" href="/issues/4230">Feature #4230</a>: Make use of existing or implement own mechanism for handling SQL schema migrations.</i> added</li></ul> Mentat - Support #4628: Consider/implement modeling of event database with SQLAlchemy declarative base.https://homeproj.cesnet.cz/issues/4628?journal_id=300362020-12-02T14:47:01ZRadko Krkoškrkos@cesnet.cz
<ul><li><strong>Tracker</strong> changed from <i>Feature</i> to <i>Support</i></li><li><strong>Category</strong> changed from <i>Development - Core</i> to <i>Research and analysis</i></li><li><strong>Assignee</strong> changed from <i>Jan Mach</i> to <i>Radko Krkoš</i></li></ul> Mentat - Support #4628: Consider/implement modeling of event database with SQLAlchemy declarative base.https://homeproj.cesnet.cz/issues/4628?journal_id=303262020-12-07T14:00:39ZRadko Krkoškrkos@cesnet.cz
<ul></ul><p>Based on the videocall discussion, the motivation behind this issue is to get rid of <code>Alembic</code>. <code>Alembic</code> is a database schema migration tool created by the same author as <code>SQLAlchemy</code> [1], mainly to support the database migrations for <code>SQLAlchemy</code>-based projects. Of course, <code>Alembic</code> can be used without <code>SQLAlchemy</code>. As it happens, both <code>mentat_main</code>, which is <code>SQLAlchemy</code> managed, and <code>mentat_events</code>, which is managed using <code>SQL</code> <code>DDL</code> descriptions, use <code>Alembic</code> for migrations, the scripts are <a class="source" href="https://homeproj.cesnet.cz/projects/mentat/repository/mentat-ng/revisions/devel/entry/lib/hawat/migrations/versions">source:/lib/hawat/migrations/versions@devel</a> and <a class="source" href="https://homeproj.cesnet.cz/projects/mentat/repository/mentat-ng/revisions/devel/entry/migrations-events/versions">source:/migrations-events/versions@devel</a> respectively. Alembic does have limited support for generating migrations scripts from <code>SQLAlchemy</code> schema changes, those must always be reviewed, fixed and tested manually. As for the <code>mentat_events</code> database, the migration scripts there surpass the abilities of automatic script generation greatly, so a schema definition using <code>SQLAlchemy</code> would not have helped much.<br />To sum it up, discarding <code>Alembic</code> does not seem realistic (without replacing it with something roughly identical) and <code>Alembic</code> does not depend on <code>SQLAlchemy</code> and can be used separately.</p>
<p>Based on discussion in issue <a class="issue tracker-2 status-5 priority-4 priority-default closed" title="Feature: Make use of existing or implement own mechanism for handling SQL schema migrations. (Closed)" href="https://homeproj.cesnet.cz/issues/4230">#4230</a>, which this issue stemmed from, the motivation for this issue was to unify "database creation, management and cleanup tasks". It would be fairly trivial to replace the direct <code>SQL</code> <code>DDL</code> description in <a class="source" href="https://homeproj.cesnet.cz/projects/mentat/repository/mentat-ng/revisions/devel/entry/lib/mentat/services/eventstorage.py">source:/lib/mentat/services/eventstorage.py@devel</a>, namely the <code>database_create()</code> and <code>index_create()</code> methods, using <code>SQLAlchemy</code> definitions and <code>DDL</code> calls ([2] is a simple tutorial containing almost all the required information). Then, for the cleanup and management tasks, a significant portion of <code>EventStorage</code> would have to be reimplemented. Of course the actual data access (and even some management tasks, such as the <code>enum</code> tables manipulation) is too complex to implement using pure <code>SQLAlchemy</code>, but that is outside of the scope of this issue anyways.</p>
<p>[1] <a class="external" href="https://pypi.org/project/alembic/">https://pypi.org/project/alembic/</a><br />[2] <a class="external" href="https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_core_creating_table.htm">https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_core_creating_table.htm</a></p> Mentat - Support #4628: Consider/implement modeling of event database with SQLAlchemy declarative base.https://homeproj.cesnet.cz/issues/4628?journal_id=303822020-12-11T18:12:33ZRadko Krkoškrkos@cesnet.cz
<ul></ul><p>CTEs (common table expressions) are in fact possible with <code>SQLAlchemy</code> [1], so <code>enum</code> table manipulation queries can be translated. However the syntax is quite lengthy and subjectively non-obvious, as illustrated in the example code in the manual.<br />Also, it is possible to define <code>GIN</code> indices in the model. [2]</p>
<p>[1] <a class="external" href="https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.cte">https://docs.sqlalchemy.org/en/14/orm/query.html#sqlalchemy.orm.Query.cte</a><br />[2] <a class="external" href="https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#postgresql-indexes">https://docs.sqlalchemy.org/en/14/dialects/postgresql.html#postgresql-indexes</a></p> Mentat - Support #4628: Consider/implement modeling of event database with SQLAlchemy declarative base.https://homeproj.cesnet.cz/issues/4628?journal_id=304552020-12-16T15:29:00ZRadko Krkoškrkos@cesnet.cz
<ul><li><strong>To be discussed</strong> set to <i>Yes</i></li></ul> Mentat - Support #4628: Consider/implement modeling of event database with SQLAlchemy declarative base.https://homeproj.cesnet.cz/issues/4628?journal_id=304672020-12-18T14:58:31ZPavel Káchaph@cesnet.cz
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>Rejected</i></li><li><strong>To be discussed</strong> deleted (<del><i>Yes</i></del>)</li></ul>Wrap-up from Radko's findings and video:
<ul>
<li>Migrations have been yet pretty much always so complex that they cannot be generated by Alembic from SQL Alchemy, but handcrafted.</li>
<li>Main and Events logic is very separate, we would not reach reasonable unification even with SQL Alchemy declaration.</li>
<li>User/Group/Network/Filter relations make sense to be modelled and enforced by means of SQL Alchemy, but we don't have any of those in Events logic (except just one relation to raw data table).</li>
<li>Table description is quite unreadable in SQL Alchemy, same for coping with cleanup and management tasks.</li>
</ul>
<p>Ergo - quite a hunk of work not balanced by benefits.</p>
<p>Mek, you've raised it up originally, so if you disagree or we do neglect something, please speak up/reopen. <span class="wiking smiley smiley-smiley" title=":)"></span></p> Mentat - Support #4628: Consider/implement modeling of event database with SQLAlchemy declarative base.https://homeproj.cesnet.cz/issues/4628?journal_id=318822021-02-16T12:56:28ZPavel Káchaph@cesnet.cz
<ul><li><strong>Target version</strong> changed from <i>Backlog</i> to <i>2.7</i></li></ul>