From b9b8e600f06558c4bdd427edb740b7b8ea882904 Mon Sep 17 00:00:00 2001
From: Adrian Moennich <adrian.moennich@cern.ch>
Date: Mon, 15 Jun 2015 17:20:01 +0200
Subject: [PATCH] Store category ids in int columns

---
 .../modules/fulltextindexes/models/categories.py   |  5 ++--
 indico/modules/users/models/favorites.py           |  2 +-
 ...151728_48177e1c4aa4_store_category_id_as_int.py | 34 ++++++++++++++++++++++
 3 files changed, 38 insertions(+), 3 deletions(-)
 create mode 100644 migrations/versions/201506151728_48177e1c4aa4_store_category_id_as_int.py

diff --git a/indico/modules/fulltextindexes/models/categories.py b/indico/modules/fulltextindexes/models/categories.py
index a0475df3a..9c17a032a 100644
--- a/indico/modules/fulltextindexes/models/categories.py
+++ b/indico/modules/fulltextindexes/models/categories.py
@@ -25,8 +25,9 @@ class IndexedCategory(db.Model):
                       {'schema': 'categories'})
 
     id = db.Column(
-        db.String,
-        primary_key=True
+        db.Integer,
+        primary_key=True,
+        autoincrement=False
     )
 
     @property
diff --git a/indico/modules/users/models/favorites.py b/indico/modules/users/models/favorites.py
index 55ed773ce..31005b142 100644
--- a/indico/modules/users/models/favorites.py
+++ b/indico/modules/users/models/favorites.py
@@ -54,7 +54,7 @@ class FavoriteCategory(db.Model):
         index=True
     )
     target_id = db.Column(
-        db.String,
+        db.Integer,
         primary_key=True,
         nullable=False
     )
diff --git a/migrations/versions/201506151728_48177e1c4aa4_store_category_id_as_int.py b/migrations/versions/201506151728_48177e1c4aa4_store_category_id_as_int.py
new file mode 100644
index 000000000..b927ef000
--- /dev/null
+++ b/migrations/versions/201506151728_48177e1c4aa4_store_category_id_as_int.py
@@ -0,0 +1,34 @@
+"""Store category id as int
+
+Revision ID: 48177e1c4aa4
+Revises: 215a0824c32c
+Create Date: 2015-06-15 13:38:05.945824
+"""
+
+import sqlalchemy as sa
+from alembic import op, context
+
+
+# revision identifiers, used by Alembic.
+revision = '48177e1c4aa4'
+down_revision = '215a0824c32c'
+
+
+def _has_legacy_ids(table, column):
+    conn = op.get_bind()
+    return conn.execute(r"SELECT EXISTS(SELECT 1 FROM {0} WHERE {1} !~ '^[1-9]\d*$' AND {1} != '0')"
+                        .format(table, column)).scalar()
+
+
+def upgrade():
+    if context.is_offline_mode():
+        raise Exception('This upgrade is only possible in online mode')
+    if _has_legacy_ids('categories.category_index', 'id') or _has_legacy_ids('users.favorite_categories', 'target_id'):
+        raise Exception('Please run the legacy_categories zodb importer first.')
+    op.execute('ALTER TABLE categories.category_index ALTER COLUMN id TYPE int USING id::int')
+    op.execute('ALTER TABLE users.favorite_categories ALTER COLUMN target_id TYPE int USING target_id::int')
+
+
+def downgrade():
+    op.alter_column('category_index', 'id', type_=sa.String, schema='categories')
+    op.alter_column('favorite_categories', 'target_id', type_=sa.String, schema='users')
-- 
2.11.4.GIT