Complete db unique key enforcement on all tables dbs

Registered by Sean Dague

* 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/, 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

Sean Dague
Needs approval
Boris Pavlovic
Series goal:
Accepted for havana
Milestone target:
milestone icon 2013.2
Started by
Boris Pavlovic
Completed by
Boris Pavlovic

Related branches



⬥ 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:
    Remove race condition (in Networks)

Addressed by:
    Remove race condition (in TaskLog)

Addressed by:
    Remove race condition (in InstanceTypes)

Addressed by:
    Remove race condition (in FloatingIps)

Gerrit topic:,topic:bp/db-enforce-unique-keys,n,z

Addressed by:
    Add missing tests for db.instance_type_* methods

Addressed by:
    Add missing tests for db.instance_type_access_* methods

Addressed by:
    Remove race condition (in InstanceTypeProjects)

Gerrit topic:,topic:bp/db-session-cleanup,n,z

Gerrit topic:,topic:bug/1145216,n,z

Addressed by:
    Refactored tests for key_pair_*() functions.

Addressed by:
    Add missing unique constraint to KeyPair model.

Addressed by:
    Add unique constraints to FixedIp and FloatingIp.

Gerrit topic:,topic:bug/1182054,n,z

Addressed by:
    Rename unique constraints due to new convention.

Gerrit topic:,topic:db-enforce-unique-keys,n,z

Addressed by:
    Add unique contraints for security groups

Addressed by:
    Session cleanup for db.security_group_* methods

Addressed by:
    Add unique constraints to Cell.

Addressed by:
    Add unique constraints to Quota.

Addressed by:
    Change unique constraint in VirtualInterface.

Addressed by:
    Add unique constraints to Service.

Addressed by:
    Minor cleanup of tests/db/

Gerrit topic:,topic:bug/1180040,n,z

Addressed by:
    Enforce block device mapping reservations within the database.

Gerrit topic:,topic:Ia615a1c1a7dc75bd19831fcd0acfc9b78c1b3f6f,n,z

Gerrit topic:,topic:db-enforce-unique-keys_virtual_interface,n,z

Gerrit topic:,topic:db-enforce-unique-keys_instance_type_extra_specs,n,z

Addressed by:
    Add unique constraints to InstanceTypeExtraSpecs.

Gerrit topic:,topic:bp/db-enforce-unique-keys_aggregate_hosts,n,z

Addressed by:
    Add unique constraints to AggregateHost.

Gerrit topic:,topic:bp/db-enforce-unique-keys_agent_builds,n,z

Addressed by:
    Add unique constraint to AgentBuild.

Gerrit topic:,topic:bug/1093458,n,z

Gerrit topic:,topic:bp/db-enforce-unique-keys_virtual_interface,n,z

Gerrit topic:,topic:bug/1156227,n,z

Gerrit topic:,topic:bp/db-enforce-unique-keys_service,n,z

Gerrit topic:,topic:bp/enforce-unique-keys_agent_build,n,z

Addressed by:
    Add unique constraint to ConsolePool.

Gerrit topic:,topic:bug/1185450,n,z

Gerrit topic:,topic:bp/db-enforce-unique-keys_instance_type_extra_specs,n,z

Gerrit topic:,topic:bp/db-enforce-unique-keys_aggregate_metadata,n,z

Addressed by:
    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.