<div dir="ltr"><div class="im" style="font-family:arial,sans-serif;font-size:13px"><div>Jay, </div><div><br></div><div><br></div><div><div class="im"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style: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><span style="color:rgb(34,34,34)">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?</span><br>
</div><div><br></div><div><br></div><div><font color="#222222">There is no method out of box that will properly compare models with migrations.. (especially in our case of supporting alembic and sqlalchemy-migrate together)</font></div>
<div><br></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style: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><span style="font-family:arial,sans-serif;font-size:13px">This is already done in some projects, IIRC... (Glance used to be this way, at least)</span><br>
<div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div><span style="font-family:arial,sans-serif;font-size:13px"><br></span></div><div><span style="font-family:arial,sans-serif;font-size:13px">And it is totally unsafe (because result of models and migrations are different)</span></div>
</div><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, Aug 27, 2013 at 8: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.<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>