placement: use separate tables for projects/users

We were a bit naughty in Iecbe0eb5717afb0b13ca90d4868a3ca5f9e8902b in
that we didn't normalize the storage of Keystone project and user
identifiers in the API DB's new consumers table. This means that we
will use a whole lot more storage for what ends up being very
repetitive data.

This patch changes the consumers DB table schema's project_id and
user_id columns from VARCHAR(255) to INT data type. This should result
in significantly faster queries for usage information since 9X the
index records can fit into a single index block in memory (36-byte
UUIDs stored in VARCHAR(255) versus 4-byte integers. The more index
records we can fit into a single page of memory, the faster both scans
and seeks will be.

Let's address this now before anything uses the consumers table.

Change-Id: I1b7357739f2a7e55c55d3acb9bd604731c4a2b32
blueprint: placement-project-user

3 files changed, 130 insertions, 2 deletions

diff --git a/nova/db/sqlalchemy/api_migrations/migrate_repo/versions/044_placement_add_projects_users.py b/nova/db/sqlalchemy/api_migrations/migrate_repo/versions/044_placement_add_projects_users.py
new file mode 100644
index 0000000000..46af2e62b5
--- /dev/null
+++ b/nova/db/sqlalchemy/api_migrations/migrate_repo/versions/044_placement_add_projects_users.py
@@ -0,0 +1,77 @@
+#    Licensed under the Apache License, Version 2.0 (the "License"); you may
+#    not use this file except in compliance with the License. You may obtain
+#    a copy of the License at
+#
+#         http://www.apache.org/licenses/LICENSE-2.0
+#
+#    Unless required by applicable law or agreed to in writing, software
+#    distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
+#    WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
+#    License for the specific language governing permissions and limitations
+#    under the License.
+
+"""Streamlines consumers table and adds projects and users table"""
+
+from migrate import UniqueConstraint
+from sqlalchemy import Column
+from sqlalchemy import DateTime
+from sqlalchemy import Integer
+from sqlalchemy import MetaData
+from sqlalchemy import String
+from sqlalchemy import Table
+
+_INDEXES_TO_REPLACE = (
+    'consumers_project_id_uuid_idx',
+    'consumers_project_id_user_id_uuid_idx',
+)
+
+
+def upgrade(migrate_engine):
+    meta = MetaData()
+    meta.bind = migrate_engine
+
+    projects = Table('projects', meta,
+        Column('id', Integer, primary_key=True, nullable=False,
+               autoincrement=True),
+        Column('external_id', String(length=255), nullable=False),
+        Column('created_at', DateTime),
+        Column('updated_at', DateTime),
+        UniqueConstraint('external_id', name='uniq_projects0external_id'),
+        mysql_engine='InnoDB',
+        mysql_charset='latin1'
+    )
+
+    projects.create(checkfirst=True)
+
+    users = Table('users', meta,
+        Column('id', Integer, primary_key=True, nullable=False,
+               autoincrement=True),
+        Column('external_id', String(length=255), nullable=False),
+        Column('created_at', DateTime),
+        Column('updated_at', DateTime),
+        UniqueConstraint('external_id', name='uniq_users0external_id'),
+        mysql_engine='InnoDB',
+        mysql_charset='latin1'
+    )
+
+    users.create(checkfirst=True)
+
+    consumers = Table('consumers', meta, autoload=True)
+    project_id_col = consumers.c.project_id
+    user_id_col = consumers.c.user_id
+
+    # NOTE(jaypipes): For PostgreSQL, we can't do col.alter(type=Integer)
+    # because NVARCHAR and INTEGER are not compatible, so we need to do this
+    # manual ALTER TABLE ... USING approach.
+    if migrate_engine.name == 'postgresql':
+        migrate_engine.execute(
+            "ALTER TABLE consumers ALTER COLUMN project_id "
+            "TYPE INTEGER USING project_id::integer"
+        )
+        migrate_engine.execute(
+            "ALTER TABLE consumers ALTER COLUMN user_id "
+            "TYPE INTEGER USING user_id::integer"
+        )
+    else:
+        project_id_col.alter(type=Integer)
+        user_id_col.alter(type=Integer)
diff --git a/nova/db/sqlalchemy/api_models.py b/nova/db/sqlalchemy/api_models.py
index 2722db4c0f..2d42036928 100644
--- a/nova/db/sqlalchemy/api_models.py
+++ b/nova/db/sqlalchemy/api_models.py
@@ -584,6 +584,36 @@ class ResourceProviderTrait(API_BASE):
                                   nullable=False)
 
 
+class Project(API_BASE):
+    """The project is the Keystone project."""
+
+    __tablename__ = 'projects'
+    __table_args__ = (
+        schema.UniqueConstraint(
+            'external_id',
+            name='uniq_projects0external_id',
+        ),
+    )
+
+    id = Column(Integer, primary_key=True, nullable=False, autoincrement=True)
+    external_id = Column(String(255), nullable=False)
+
+
+class User(API_BASE):
+    """The user is the Keystone user."""
+
+    __tablename__ = 'users'
+    __table_args__ = (
+        schema.UniqueConstraint(
+            'external_id',
+            name='uniq_users0external_id',
+        ),
+    )
+
+    id = Column(Integer, primary_key=True, nullable=False, autoincrement=True)
+    external_id = Column(String(255), nullable=False)
+
+
 class Consumer(API_BASE):
     """Represents a resource consumer."""
 
@@ -597,5 +627,5 @@ class Consumer(API_BASE):
 
     id = Column(Integer, primary_key=True, nullable=False, autoincrement=True)
     uuid = Column(String(36), nullable=False)
-    project_id = Column(String(255), nullable=False)
-    user_id = Column(String(255), nullable=False)
+    project_id = Column(Integer, nullable=False)
+    user_id = Column(Integer, nullable=False)
diff --git a/nova/tests/functional/db/api/test_migrations.py b/nova/tests/functional/db/api/test_migrations.py
index fc6ffd5c61..82309c6b02 100644
--- a/nova/tests/functional/db/api/test_migrations.py
+++ b/nova/tests/functional/db/api/test_migrations.py
@@ -620,6 +620,27 @@ class NovaAPIMigrationsWalk(test_migrations.WalkVersionsMixin):
                                'consumers_project_id_user_id_uuid_idx')
         self.assertUniqueConstraintExists(engine, 'consumers', ['uuid'])
 
+    def _check_044(self, engine, data):
+        for column in ['created_at', 'updated_at', 'id', 'external_id']:
+            self.assertColumnExists(engine, 'projects', column)
+            self.assertColumnExists(engine, 'users', column)
+
+        self.assertUniqueConstraintExists(engine, 'projects', ['external_id'])
+        self.assertUniqueConstraintExists(engine, 'users', ['external_id'])
+
+        # We needed to drop and recreate columns and indexes on consumers, so
+        # check that worked out properly
+        self.assertColumnExists(engine, 'consumers', 'project_id')
+        self.assertColumnExists(engine, 'consumers', 'user_id')
+        self.assertIndexExists(
+            engine, 'consumers',
+            'consumers_project_id_uuid_idx',
+        )
+        self.assertIndexExists(
+            engine, 'consumers',
+            'consumers_project_id_user_id_uuid_idx',
+        )
+
 
 class TestNovaAPIMigrationsWalkSQLite(NovaAPIMigrationsWalk,
                                       test_base.DbTestCase,