# HG changeset patch
# User Mads Kiilerich <mads@kiilerich.com>
# Date 2019-12-29 15:35:06
# Node ID 1ba3aeefe0336c526f8c578bb26583fafaa49f52
# Parent  44e18bd4c3b2402223d8267f79e5ff34f7e7135e

ssh: drop usk_public_key_idx again

Essentially a backout of d2a97f73fa1f and the
4851d15bc437_db_migration_step_after_95c01895c006_ alembic step.

We can't reliably have full index on fields with unbounded length. The
upgrade step has been reported to fail on MySQL [1]:

    sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError)
    (1170, "BLOB/TEXT column 'public_key' used in key specification without
    a key length") [SQL: u'CREATE INDEX usk_public_key_idx ON user_ssh_keys
    (public_key)'] (Background on this error at: http://sqlalche.me/e/e3q8)

And we really don't need this index ... especially now when we use
fingerprints for key deletion instead of looking up by the full public key.


[1] https://lists.sfconservancy.org/pipermail/kallithea-general/2019q4/003068.html

diff --git a/kallithea/alembic/versions/4851d15bc437_db_migration_step_after_95c01895c006_.py b/kallithea/alembic/versions/4851d15bc437_db_migration_step_after_95c01895c006_.py
--- a/kallithea/alembic/versions/4851d15bc437_db_migration_step_after_95c01895c006_.py
+++ b/kallithea/alembic/versions/4851d15bc437_db_migration_step_after_95c01895c006_.py
@@ -31,14 +31,20 @@ from alembic import op
 
 
 def upgrade():
-    meta = sa.MetaData()
-    meta.reflect(bind=op.get_bind())
+    pass
+    # The following upgrade step turned out to be a bad idea. A later step
+    # "d7ec25b66e47_ssh_drop_usk_public_key_idx_again" will remove the index
+    # again if it exists ... but we shouldn't even try to create it.
 
-    if not any(i.name == 'usk_public_key_idx' for i in meta.tables['user_ssh_keys'].indexes):
-        with op.batch_alter_table('user_ssh_keys', schema=None) as batch_op:
-            batch_op.create_index('usk_public_key_idx', ['public_key'], unique=False)
+    #meta = sa.MetaData()
+    #meta.reflect(bind=op.get_bind())
+
+    #if not any(i.name == 'usk_public_key_idx' for i in meta.tables['user_ssh_keys'].indexes):
+    #    with op.batch_alter_table('user_ssh_keys', schema=None) as batch_op:
+    #        batch_op.create_index('usk_public_key_idx', ['public_key'], unique=False)
 
 
 def downgrade():
-    with op.batch_alter_table('user_ssh_keys', schema=None) as batch_op:
-        batch_op.drop_index('usk_public_key_idx')
+    if any(i.name == 'usk_public_key_idx' for i in meta.tables['user_ssh_keys'].indexes):
+        with op.batch_alter_table('user_ssh_keys', schema=None) as batch_op:
+            batch_op.drop_index('usk_public_key_idx')
diff --git a/kallithea/alembic/versions/d7ec25b66e47_ssh_drop_usk_public_key_idx_again.py b/kallithea/alembic/versions/d7ec25b66e47_ssh_drop_usk_public_key_idx_again.py
new file mode 100644
--- /dev/null
+++ b/kallithea/alembic/versions/d7ec25b66e47_ssh_drop_usk_public_key_idx_again.py
@@ -0,0 +1,43 @@
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program.  If not, see <http://www.gnu.org/licenses/>.
+
+"""ssh: drop usk_public_key_idx again
+
+Revision ID: d7ec25b66e47
+Revises: 4851d15bc437
+Create Date: 2019-12-29 15:33:10.982003
+
+"""
+
+# The following opaque hexadecimal identifiers ("revisions") are used
+# by Alembic to track this migration script and its relations to others.
+revision = 'd7ec25b66e47'
+down_revision = '4851d15bc437'
+branch_labels = None
+depends_on = None
+
+import sqlalchemy as sa
+from alembic import op
+
+
+def upgrade():
+    meta = sa.MetaData()
+    meta.reflect(bind=op.get_bind())
+
+    if any(i.name == 'usk_public_key_idx' for i in meta.tables['user_ssh_keys'].indexes):
+        with op.batch_alter_table('user_ssh_keys', schema=None) as batch_op:
+            batch_op.drop_index('usk_public_key_idx')
+
+
+def downgrade():
+    pass
diff --git a/kallithea/model/db.py b/kallithea/model/db.py
--- a/kallithea/model/db.py
+++ b/kallithea/model/db.py
@@ -2518,7 +2518,6 @@ class Gist(Base, BaseDbModel):
 class UserSshKeys(Base, BaseDbModel):
     __tablename__ = 'user_ssh_keys'
     __table_args__ = (
-        Index('usk_public_key_idx', 'public_key'),
         Index('usk_fingerprint_idx', 'fingerprint'),
         UniqueConstraint('fingerprint'),
         _table_args_default_dict