kallithea Changeset - 1ba3aeefe033

Changeset - 1ba3aeefe033

Parent rev.

Child rev.

[Not reviewed]

stable

0 2 1

Mads Kiilerich - 6 years ago 2019-12-29 15:35:06
mads@kiilerich.com

Grafted from: a85a011f7750

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

3 files changed with 56 insertions and 8 deletions:

kallithea/alembic/versions/4851d15bc437_db_migration_step_after_95c01895c006_.py

kallithea/alembic/versions/d7ec25b66e47_ssh_drop_usk_public_key_idx_again.py

kallithea/model/db.py

0 comments (0 inline, 0 general)

kallithea/alembic/versions/4851d15bc437_db_migration_step_after_95c01895c006_.py

➞

Show inline comments

@@ @@ -22,23 +22,29 @@ Create Date: 2019-11-24 02:51:14.029583 @@
 # The following opaque hexadecimal identifiers ("revisions") are used
 # by Alembic to track this migration script and its relations to others.
 revision = '4851d15bc437'
 down_revision = '151b4a4e8c48'
 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())
     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')

kallithea/alembic/versions/d7ec25b66e47_ssh_drop_usk_public_key_idx_again.py

➞

Show inline comments

@@ new file 100644 @@
 # 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

kallithea/model/db.py

➞

Show inline comments

@@ @@ -2509,25 +2509,24 @@ class Gist(Base, BaseDbModel): @@
     @property
     def scm_instance(self):
         from kallithea.lib.vcs import get_repo
         base_path = self.base_path()
         return get_repo(os.path.join(*map(safe_str,
                                           [base_path, self.gist_access_id])))
 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
+    )
     __mapper_args__ = {}
     user_ssh_key_id = Column(Integer(), primary_key=True)
     user_id = Column(Integer(), ForeignKey('users.user_id'), nullable=False)
     _public_key = Column('public_key', UnicodeText(), nullable=False)
     description = Column(UnicodeText(), nullable=False)
     fingerprint = Column(String(255), nullable=False, unique=True)
     created_on = Column(DateTime(timezone=False), nullable=False, default=datetime.datetime.now)

0 comments (0 inline, 0 general)