<div dir="ltr"><br><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Aug 27, 2013 at 12:30 PM, Jay Pipes <span dir="ltr"><<a href="mailto:jaypipes@gmail.com" target="_blank">jaypipes@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">On 08/27/2013 04:32 AM, Boris Pavlovic wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Jay,<br>
<br>
I should probably share to you about our work around DB.<br>
<br>
Migrations should be run only in production and only for production<br>
backends (e.g. psql and mysql)<br>
In tests we should use Schemas created by Models<br>
(BASE.metadata.create_all())<br>
</blockquote>
<br></div>
Agree on both.<div class="im"><br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
We are not able to use in this approach in moment  because we don't have<br>
any mechanism to check that MODELS and SCHEMAS are EQUAL.<br>
And actually MODELS and SCHEMAS are DIFFERENT.<br>
</blockquote>
<br></div>
Sorry, I don't understand the connection... how does not having a codified way of determining the difference between model and schema (BTW, this does exist in sqlalchemy-migrate... look at the compare_model_to_db method) not allow you to use metadata.create_all() in tests or mean that you can't run migrations only in production?<div class="im">
<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
E.g. in Celiometer we have BP that syncs models and migration<br>
<a href="https://blueprints.launchpad.net/ceilometer/+spec/ceilometer-db-sync-models-with-migrations" target="_blank">https://blueprints.launchpad.<u></u>net/ceilometer/+spec/<u></u>ceilometer-db-sync-models-<u></u>with-migrations</a><br>

(in other projects we are doing the same)<br>
<br>
And also we are working around (oslo) generic tests that checks that<br>
models and migrations are equal:<br>
<a href="https://review.openstack.org/#/c/42307/" target="_blank">https://review.openstack.org/#<u></u>/c/42307/</a><br>
</blockquote>
<br></div>
OK, cool.<div class="im"><br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
So in our roadmap (in this case is):<br>
1) Soft switch to alembic (with code that allows to have sqla-migrate<br>
and alembic migration in the same time)<br>
</blockquote>
<br></div>
I don't see the point in this at all... I would rather see patches that just switch to Alembic and get rid of SQLAlchemy-migrate. Create an initial Alembic migration that has the last state of the database schema under SQLAlchemy-migrate... and then delete SA-Migrate.</blockquote>
<div><br></div><div>We had a rather long discussion about this on the mailing list a while back. We decided not to spend time changing the existing migrations because we didn't want to introduce differences for anyone doing continuous deployment. The work to add alembic was supposed to mark the soft-switch, and it looks like you're the (unlucky) first person to try to create an actual alembic migration script.</div>
<div><br></div><div>We have an agenda item on the ceilometer meeting for today to discuss what to do. At this point I think we should stick with sqlalchemy-migrate to avoid causing delays before the H3 deadline.</div><div>
<br></div><div>Dou</div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im"><br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
2) Sync Models and Migrations (fix DB schemas also)<br>
3) Add from oslo generic test that checks all this stuff<br>
4) Use BASE.create_all() for Schema creation instead of migrations.<br>
</blockquote>
<br></div>
This is already done in some projects, IIRC... (Glance used to be this way, at least)<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">
But in OpenStack is not so simple to implement such huge changes, so it<br>
take some time=)<br>
<br>
<br>
Best regards,<br>
Boris Pavlovic<br>
---<br>
Mirantis Inc.<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
<br>
On Tue, Aug 27, 2013 at 12:02 AM, Jay Pipes <<a href="mailto:jaypipes@gmail.com" target="_blank">jaypipes@gmail.com</a><br></div><div class="im">
<mailto:<a href="mailto:jaypipes@gmail.com" target="_blank">jaypipes@gmail.com</a>>> wrote:<br>
<br>
    On 08/26/2013 03:40 PM, Herndon, John Luke (HPCS - Ft. Collins) wrote:<br>
<br>
        Jay -<br>
<br>
        It looks there is an error in the migration script that causes<br>
        it to abort:<br>
<br>
        AttributeError: 'ForeignKeyConstraint' object has no attribute<br>
        'drop'<br>
<br>
        My guess is the migration runs on the first test, creates event<br>
        types<br>
        table fine, but exits with the above error, so migration is not<br>
        "complete". Thus every subsequent test tries to migrate the db, and<br>
        notices that event types already exists.<br>
<br>
<br>
    I'd corrected that particular mistake and pushed an updated<br>
    migration script.<br>
<br>
    Best,<br>
    -jay<br>
<br>
<br>
<br>
        -john<br>
<br>
        On 8/26/13 1:15 PM, "Jay Pipes" <<a href="mailto:jaypipes@gmail.com" target="_blank">jaypipes@gmail.com</a><br></div><div class="im">
        <mailto:<a href="mailto:jaypipes@gmail.com" target="_blank">jaypipes@gmail.com</a>>> wrote:<br>
<br>
            I just noticed that every single test case for SQL-driver<br>
            storage is<br>
            executing every single migration upgrade before every single<br>
            test case<br>
            run:<br>
<br></div><div class="im">
            <a href="https://github.com/openstack/__ceilometer/blob/master/__ceilometer/tests/db.py" target="_blank">https://github.com/openstack/_<u></u>_ceilometer/blob/master/__<u></u>ceilometer/tests/db.py</a><br>
            <<a href="https://github.com/openstack/ceilometer/blob/master/ceilometer/tests/db.py" target="_blank">https://github.com/openstack/<u></u>ceilometer/blob/master/<u></u>ceilometer/tests/db.py</a>><br>
            #L46<br>
<br>
            <a href="https://github.com/openstack/__ceilometer/blob/master/__ceilometer/storage/imp" target="_blank">https://github.com/openstack/_<u></u>_ceilometer/blob/master/__<u></u>ceilometer/storage/imp</a><br></div>
<div class="im">
            <<a href="https://github.com/openstack/ceilometer/blob/master/ceilometer/storage/imp" target="_blank">https://github.com/openstack/<u></u>ceilometer/blob/master/<u></u>ceilometer/storage/imp</a>><br>
            l_sqlalchemy.py#L153<br>
<br>
            instead of simply creating a new database schema from the<br>
            models in the<br>
            current source code base using a call to<br></div>
            sqlalchemy.MetaData.create___<u></u>all().<div class="im"><br>
<br>
            This results in re-running migrations over and over again,<br>
            instead of<br>
            having dedicated migration tests that would test each migration<br>
            individually, as is the case in projects like Glance...<br>
<br>
            Is this intentional?<br>
<br>
            Best,<br>
            -jay<br>
<br>
            On 08/26/2013 02:59 PM, Sandy Walsh wrote:<br>
<br>
                I'm getting the same problem with a different migration<br>
                (mine is<br>
                complaining that a column already exists)<br>
<br></div>
                <a href="http://paste.openstack.org/__show/44512/" target="_blank">http://paste.openstack.org/__<u></u>show/44512/</a><div class="im"><br>
                <<a href="http://paste.openstack.org/show/44512/" target="_blank">http://paste.openstack.org/<u></u>show/44512/</a>><br>
<br>
                I've compared it to the other migrations and it seems fine.<br>
<br>
                -S<br>
<br>
                On 08/26/2013 02:34 PM, Jay Pipes wrote:<br>
<br>
                    Hey all,<br>
<br>
                    I'm trying to figure out what is going wrong with my<br>
                    code for this<br>
                    patch:<br>
<br></div>
                    <a href="https://review.openstack.org/__41316" target="_blank">https://review.openstack.org/_<u></u>_41316</a><div><div class="h5"><br>
                    <<a href="https://review.openstack.org/41316" target="_blank">https://review.openstack.org/<u></u>41316</a>><br>
<br>
                    I had previously added a sqlalchemy-migrate<br>
                    migration script to add an<br>
                    event_type table, and had that working, but then was<br>
                    asked to instead<br>
                    use Alembic for migrations. So, I removed the<br>
                    sqlalchemy-migrate<br>
                    migration file and added an Alembic migration [1].<br>
<br>
                    Unfortunately, I am getting the following error when<br>
                    running tests:<br>
<br>
                    OperationalError: (OperationalError) table<br>
                    event_type already exists<br>
                    u'\nCREATE TABLE event_type (\n\tid INTEGER NOT<br>
                    NULL, \n\t"desc"<br>
                    VARCHAR(255), \n\tPRIMARY KEY (id), \n\tUNIQUE<br>
                    ("desc")\n)\n\n' ()<br>
<br>
                    The migration adds the event_type table. I've seen<br>
                    this error occur<br>
                    before when using SQLite due to SQLite's ALTER TABLE<br>
                    statement not<br>
                    allowing the rename of a column. In the<br>
                    sqlalchemy-migrate migration, I<br>
                    had a specialized SQLite migration upgrade [2] and<br>
                    downgrade [3]<br>
                    script,<br>
                    but I'm not sure how I am supposed to handle this in<br>
                    Alembic. Could<br>
                    someone help me out?<br>
<br>
                    Thanks,<br>
                    -jay<br>
<br>
                    [1]<br>
<br></div></div><div><div class="h5">
                    <a href="https://review.openstack.org/#__/c/41316/16/ceilometer/__storage/sqlalchemy/" target="_blank">https://review.openstack.org/#<u></u>__/c/41316/16/ceilometer/__<u></u>storage/sqlalchemy/</a><br>

                    <<a href="https://review.openstack.org/#/c/41316/16/ceilometer/storage/sqlalchemy/" target="_blank">https://review.openstack.org/<u></u>#/c/41316/16/ceilometer/<u></u>storage/sqlalchemy/</a>><br>

                    alembic/versions/49036daaaafd_<u></u>__add_event_types.py<br>
<br>
                    [2]<br>
<br>
                    <a href="https://review.openstack.org/#__/c/41316/14/ceilometer/__storage/sqlalchemy/" target="_blank">https://review.openstack.org/#<u></u>__/c/41316/14/ceilometer/__<u></u>storage/sqlalchemy/</a><br>

                    <<a href="https://review.openstack.org/#/c/41316/14/ceilometer/storage/sqlalchemy/" target="_blank">https://review.openstack.org/<u></u>#/c/41316/14/ceilometer/<u></u>storage/sqlalchemy/</a>><br>

                    migrate_repo/versions/013___<u></u>sqlite_upgrade.sql<br>
<br>
                    [3]<br>
<br>
                    <a href="https://review.openstack.org/#__/c/41316/14/ceilometer/__storage/sqlalchemy/" target="_blank">https://review.openstack.org/#<u></u>__/c/41316/14/ceilometer/__<u></u>storage/sqlalchemy/</a><br>

                    <<a href="https://review.openstack.org/#/c/41316/14/ceilometer/storage/sqlalchemy/" target="_blank">https://review.openstack.org/<u></u>#/c/41316/14/ceilometer/<u></u>storage/sqlalchemy/</a>><br>

                    migrate_repo/versions/013___<u></u>sqlite_downgrade.sql<br>
<br>
<br>
                    ______________________________<u></u>___________________<br>
                    OpenStack-dev mailing list<br>
                    OpenStack-dev@lists.openstack.<u></u>__org<br>
                    <mailto:<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.<u></u>openstack.org</a>><br>
                    <a href="http://lists.openstack.org/__cgi-bin/mailman/listinfo/__openstack-dev" target="_blank">http://lists.openstack.org/__<u></u>cgi-bin/mailman/listinfo/__<u></u>openstack-dev</a><br>
                    <<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a>><br>
<br>
<br>
<br>
            ______________________________<u></u>___________________<br>
            OpenStack-dev mailing list<br>
            OpenStack-dev@lists.openstack.<u></u>__org<br>
            <mailto:<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.<u></u>openstack.org</a>><br>
            <a href="http://lists.openstack.org/__cgi-bin/mailman/listinfo/__openstack-dev" target="_blank">http://lists.openstack.org/__<u></u>cgi-bin/mailman/listinfo/__<u></u>openstack-dev</a><br>
            <<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a>><br>
<br>
<br>
            ______________________________<u></u>___________________<br>
            OpenStack-dev mailing list<br>
            OpenStack-dev@lists.openstack.<u></u>__org<br>
            <mailto:<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.<u></u>openstack.org</a>><br>
            <a href="http://lists.openstack.org/__cgi-bin/mailman/listinfo/__openstack-dev" target="_blank">http://lists.openstack.org/__<u></u>cgi-bin/mailman/listinfo/__<u></u>openstack-dev</a><br>
            <<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a>><br>
<br>
<br>
<br>
    ______________________________<u></u>___________________<br>
    OpenStack-dev mailing list<br>
    OpenStack-dev@lists.openstack.<u></u>__org<br>
    <mailto:<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.<u></u>openstack.org</a>><br></div></div>
    <a href="http://lists.openstack.org/__cgi-bin/mailman/listinfo/__openstack-dev" target="_blank">http://lists.openstack.org/__<u></u>cgi-bin/mailman/listinfo/__<u></u>openstack-dev</a> <<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a>><div class="im">
<br>
<br>
<br>
<br>
<br>
______________________________<u></u>_________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.openstack.<u></u>org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a><br>
<br>
</div></blockquote><div class="HOEnZb"><div class="h5">
<br>
<br>
______________________________<u></u>_________________<br>
OpenStack-dev mailing list<br>
<a href="mailto:OpenStack-dev@lists.openstack.org" target="_blank">OpenStack-dev@lists.openstack.<u></u>org</a><br>
<a href="http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev" target="_blank">http://lists.openstack.org/<u></u>cgi-bin/mailman/listinfo/<u></u>openstack-dev</a><br>
</div></div></blockquote></div><br></div></div>