Complete db unique key enforcement on all tables dbs

Registered by Sean Dague on 2013-02-19

* Add real unique indexes on (`col`, `deleted`), and when deleting any row, set `deleted`=`id` instead of `deleted`=1.
* Handle duplicate key errors in a sane way.
* In db/sqlalchemy/api.py, replace occurrences of SELECT + INSERT-or-UPDATE with upserts. In mysql, this is "INSERT ... ON DUPLICATE KEY UPDATE". In postgres, it is a bit more involved.
* Add to models missing unique constraints (just to show what UC model have)

Blueprint information

Status:
Complete
Approver:
Sean Dague
Priority:
High
Drafter:
None
Direction:
Needs approval
Assignee:
Boris Pavlovic
Definition:
Approved
Series goal:
Accepted for havana
Implementation:
Implemented
Milestone target:
milestone icon 2013.2
Started by
Boris Pavlovic on 2013-03-11
Completed by
Boris Pavlovic on 2013-08-22

Related branches

Sprints

Whiteboard

⬥ Tables that should have UC:

✓ task_log:
    UC on ('task_name', 'host', 'period_beginning', 'period_ending')

✓ networks:
    UC on ('vlan', 'deleted')

✓ floating_ips:
    UC on (address, deleted)

✓ instance_types:
   UC on (name, deleted) and (flavorid, deleted)

✓ instance_type_projects
   UC on (project_id, instance_type_id, deleted)

✓ instance_type_extra_specs:
   UC on (instance_type_id, key, deleted)

✓ virtual_interfaces:
   At this moment this method has UC on column address, we should change it to (address, deleted)
   and use soft_deleted instead of delete.

✓ services:
   UC on columns (host, topic, deleted), (host, binary, deleted).

✓ security_groups:
   UC on columns (project_id, name, deleted)

✓ fixed_ips:
    UC on (address, deleted)

✓ cells:
    UC on (name, deleted)

✓ quotas:
    UC on (project_id, resource, deleted)

✓ aggregate_hosts:
    UC on (host, aggregate_id, deleted)

⚒ aggregate_metadata:
     UC on (aggregate_id, key, deleted)

✓ agent_builds:
    UC on (hypervisor, os, architecture, deleted)

----

Addressed by: https://review.openstack.org/22155
    Remove race condition (in Networks)

Addressed by: https://review.openstack.org/20936/
    Remove race condition (in TaskLog)

Addressed by: https://review.openstack.org/22477
    Remove race condition (in InstanceTypes)

Addressed by: https://review.openstack.org/23466
    Remove race condition (in FloatingIps)

Gerrit topic: https://review.openstack.org/#q,topic:bp/db-enforce-unique-keys,n,z

Addressed by: https://review.openstack.org/25252
    Add missing tests for db.instance_type_* methods

Addressed by: https://review.openstack.org/25253
    Add missing tests for db.instance_type_access_* methods

Addressed by: https://review.openstack.org/25254
    Remove race condition (in InstanceTypeProjects)

Gerrit topic: https://review.openstack.org/#q,topic:bp/db-session-cleanup,n,z

Gerrit topic: https://review.openstack.org/#q,topic:bug/1145216,n,z

Addressed by: https://review.openstack.org/29062
    Refactored tests for key_pair_*() functions.

Addressed by: https://review.openstack.org/29063
    Add missing unique constraint to KeyPair model.

Addressed by: https://review.openstack.org/29364
    Add unique constraints to FixedIp and FloatingIp.

Gerrit topic: https://review.openstack.org/#q,topic:bug/1182054,n,z

Addressed by: https://review.openstack.org/30108
    Rename unique constraints due to new convention.

Gerrit topic: https://review.openstack.org/#q,topic:db-enforce-unique-keys,n,z

Addressed by: https://review.openstack.org/32886
    Add unique contraints for security groups

Addressed by: https://review.openstack.org/31619
    Session cleanup for db.security_group_* methods

Addressed by: https://review.openstack.org/33250
    Add unique constraints to Cell.

Addressed by: https://review.openstack.org/33264
    Add unique constraints to Quota.

Addressed by: https://review.openstack.org/33299
    Change unique constraint in VirtualInterface.

Addressed by: https://review.openstack.org/33653
    Add unique constraints to Service.

Addressed by: https://review.openstack.org/33678
    Minor cleanup of tests/db/test_db_api.py

Gerrit topic: https://review.openstack.org/#q,topic:bug/1180040,n,z

Addressed by: https://review.openstack.org/33527
    Enforce block device mapping reservations within the database.

Gerrit topic: https://review.openstack.org/#q,topic:Ia615a1c1a7dc75bd19831fcd0acfc9b78c1b3f6f,n,z

Gerrit topic: https://review.openstack.org/#q,topic:db-enforce-unique-keys_virtual_interface,n,z

Gerrit topic: https://review.openstack.org/#q,topic:db-enforce-unique-keys_instance_type_extra_specs,n,z

Addressed by: https://review.openstack.org/34578
    Add unique constraints to InstanceTypeExtraSpecs.

Gerrit topic: https://review.openstack.org/#q,topic:bp/db-enforce-unique-keys_aggregate_hosts,n,z

Addressed by: https://review.openstack.org/34746
    Add unique constraints to AggregateHost.

Gerrit topic: https://review.openstack.org/#q,topic:bp/db-enforce-unique-keys_agent_builds,n,z

Addressed by: https://review.openstack.org/35259
    Add unique constraint to AgentBuild.

Gerrit topic: https://review.openstack.org/#q,topic:bug/1093458,n,z

Gerrit topic: https://review.openstack.org/#q,topic:bp/db-enforce-unique-keys_virtual_interface,n,z

Gerrit topic: https://review.openstack.org/#q,topic:bug/1156227,n,z

Gerrit topic: https://review.openstack.org/#q,topic:bp/db-enforce-unique-keys_service,n,z

Gerrit topic: https://review.openstack.org/#q,topic:bp/enforce-unique-keys_agent_build,n,z

Addressed by: https://review.openstack.org/35792
    Add unique constraint to ConsolePool.

Gerrit topic: https://review.openstack.org/#q,topic:bug/1185450,n,z

Gerrit topic: https://review.openstack.org/#q,topic:bp/db-enforce-unique-keys_instance_type_extra_specs,n,z

Gerrit topic: https://review.openstack.org/#q,topic:bp/db-enforce-unique-keys_aggregate_metadata,n,z

Addressed by: https://review.openstack.org/36880
    Add unique constraint to AggregateMetadata.

(?)

Work Items

Dependency tree

* Blueprints in grey have been implemented.

This blueprint contains Public information 
Everyone can see this information.