Mysql fixes

- force INNODB engine
- set utf8 charset
- fix convert_unicode flag in .ini files
- fix constraints and FK

development.ini

@@ -213,7 +213,7 b' logview.pylons.util = #eee'
 sqlalchemy.db1.url = postgresql://postgres:qwe@localhost/rhodecode
 sqlalchemy.db1.echo = false
 sqlalchemy.db1.pool_recycle = 3600
-sqlalchemy.convert_unicode = true
+sqlalchemy.db1.convert_unicode = true
 
 ################################
 ### LOGGING CONFIGURATION   ####

production.ini

@@ -213,7 +213,7 b' logview.pylons.util = #eee'
 sqlalchemy.db1.url = postgresql://postgres:qwe@localhost/rhodecode
 sqlalchemy.db1.echo = false
 sqlalchemy.db1.pool_recycle = 3600
-sqlalchemy.convert_unicode = true
+sqlalchemy.db1.convert_unicode = true
 
 ################################
 ### LOGGING CONFIGURATION   ####

rhodecode/config/deployment.ini_tmpl

@@ -223,7 +223,7 b' sqlalchemy.db1.url = sqlite:///%(here)s/'
 
 sqlalchemy.db1.echo = false
 sqlalchemy.db1.pool_recycle = 3600
-sqlalchemy.convert_unicode = true
+sqlalchemy.db1.convert_unicode = true
 
 ################################
 ### LOGGING CONFIGURATION   ####

rhodecode/model/__init__.py

@@ -85,14 +85,14 b' class BaseModel(object):'
 
         if isinstance(instance, cls):
             return instance
-        elif isinstance(instance, int) or str(instance).isdigit():
+        elif isinstance(instance, (int, long)) or str(instance).isdigit():
             return cls.get(instance)
         else:
             if instance:
                 if callback is None:
                     raise Exception(
-                        'given object must be int or Instance of %s got %s, '
-                        'no callback provided' % (cls, type(instance))
+                        'given object must be int, long or Instance of %s '
+                        'got %s, no callback provided' % (cls, type(instance))
                     )
                 else:
                     return callback(instance)

rhodecode/model/db.py

@@ -151,7 +151,8 b' class RhodeCodeSetting(Base, BaseModel):'
     __tablename__ = 'rhodecode_settings'
     __table_args__ = (
         UniqueConstraint('app_settings_name'),
-        {'extend_existing': True}
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'}
     )
     app_settings_id = Column("app_settings_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)
     app_settings_name = Column("app_settings_name", String(length=255, convert_unicode=False, assert_unicode=None), nullable=True, unique=None, default=None)
@@ -225,7 +226,8 b' class RhodeCodeUi(Base, BaseModel):'
     __tablename__ = 'rhodecode_ui'
     __table_args__ = (
         UniqueConstraint('ui_key'),
-        {'extend_existing': True}
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'}
     )
 
     HOOK_UPDATE = 'changegroup.update'
@@ -275,7 +277,8 b' class User(Base, BaseModel):'
     __tablename__ = 'users'
     __table_args__ = (
         UniqueConstraint('username'), UniqueConstraint('email'),
-        {'extend_existing': True}
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'}
     )
     user_id = Column("user_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)
     username = Column("username", String(length=255, convert_unicode=False, assert_unicode=None), nullable=True, unique=None, default=None)
@@ -295,7 +298,8 b' class User(Base, BaseModel):'
     repositories = relationship('Repository')
     user_followers = relationship('UserFollowing', primaryjoin='UserFollowing.follows_user_id==User.user_id', cascade='all')
     repo_to_perm = relationship('UserRepoToPerm', primaryjoin='UserRepoToPerm.user_id==User.user_id', cascade='all')
-
+    repo_group_to_perm = relationship('UserRepoGroupToPerm', primaryjoin='UserRepoGroupToPerm.user_id==User.user_id', cascade='all')
+    
     group_member = relationship('UsersGroupMember', cascade='all')
 
     notifications = relationship('UserNotification',)
@@ -387,7 +391,10 b' class User(Base, BaseModel):'
 
 class UserLog(Base, BaseModel):
     __tablename__ = 'user_logs'
-    __table_args__ = {'extend_existing': True}
+    __table_args__ = (
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'},
+    )
     user_log_id = Column("user_log_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)
     user_id = Column("user_id", Integer(), ForeignKey('users.user_id'), nullable=False, unique=None, default=None)
     repository_id = Column("repository_id", Integer(), ForeignKey('repositories.repo_id'), nullable=True)
@@ -406,7 +413,10 b' class UserLog(Base, BaseModel):'
 
 class UsersGroup(Base, BaseModel):
     __tablename__ = 'users_groups'
-    __table_args__ = {'extend_existing': True}
+    __table_args__ = (
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'},
+    )
 
     users_group_id = Column("users_group_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)
     users_group_name = Column("users_group_name", String(length=255, convert_unicode=False, assert_unicode=None), nullable=False, unique=True, default=None)
@@ -414,6 +424,7 b' class UsersGroup(Base, BaseModel):'
 
     members = relationship('UsersGroupMember', cascade="all, delete, delete-orphan", lazy="joined")
     users_group_to_perm = relationship('UsersGroupToPerm', cascade='all')
+    users_group_repo_to_perm = relationship('UsersGroupRepoToPerm', cascade='all')
 
     def __repr__(self):
         return '<userGroup(%s)>' % (self.users_group_name)
@@ -444,7 +455,10 b' class UsersGroup(Base, BaseModel):'
 
 class UsersGroupMember(Base, BaseModel):
     __tablename__ = 'users_groups_members'
-    __table_args__ = {'extend_existing': True}
+    __table_args__ = (
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'},
+    )
 
     users_group_member_id = Column("users_group_member_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)
     users_group_id = Column("users_group_id", Integer(), ForeignKey('users_groups.users_group_id'), nullable=False, unique=None, default=None)
@@ -462,7 +476,8 b' class Repository(Base, BaseModel):'
     __tablename__ = 'repositories'
     __table_args__ = (
         UniqueConstraint('repo_name'),
-        {'extend_existing': True},
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'},
     )
 
     repo_id = Column("repo_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)
@@ -711,7 +726,8 b' class RepoGroup(Base, BaseModel):'
     __table_args__ = (
         UniqueConstraint('group_name', 'group_parent_id'),
         CheckConstraint('group_id != group_parent_id'),
-        {'extend_existing': True},
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'},
     )
     __mapper_args__ = {'order_by': 'group_name'}
 
@@ -838,7 +854,10 b' class RepoGroup(Base, BaseModel):'
 
 class Permission(Base, BaseModel):
     __tablename__ = 'permissions'
-    __table_args__ = {'extend_existing': True}
+    __table_args__ = (
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'},
+    )
     permission_id = Column("permission_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)
     permission_name = Column("permission_name", String(length=255, convert_unicode=False, assert_unicode=None), nullable=True, unique=None, default=None)
     permission_longname = Column("permission_longname", String(length=255, convert_unicode=False, assert_unicode=None), nullable=True, unique=None, default=None)
@@ -875,7 +894,8 b' class UserRepoToPerm(Base, BaseModel):'
     __tablename__ = 'repo_to_perm'
     __table_args__ = (
         UniqueConstraint('user_id', 'repository_id', 'permission_id'),
-        {'extend_existing': True}
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'}
     )
     repo_to_perm_id = Column("repo_to_perm_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)
     user_id = Column("user_id", Integer(), ForeignKey('users.user_id'), nullable=False, unique=None, default=None)
@@ -903,7 +923,8 b' class UserToPerm(Base, BaseModel):'
     __tablename__ = 'user_to_perm'
     __table_args__ = (
         UniqueConstraint('user_id', 'permission_id'),
-        {'extend_existing': True}
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'}
     )
     user_to_perm_id = Column("user_to_perm_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)
     user_id = Column("user_id", Integer(), ForeignKey('users.user_id'), nullable=False, unique=None, default=None)
@@ -917,7 +938,8 b' class UsersGroupRepoToPerm(Base, BaseMod'
     __tablename__ = 'users_group_repo_to_perm'
     __table_args__ = (
         UniqueConstraint('repository_id', 'users_group_id', 'permission_id'),
-        {'extend_existing': True}
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'}
     )
     users_group_to_perm_id = Column("users_group_to_perm_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)
     users_group_id = Column("users_group_id", Integer(), ForeignKey('users_groups.users_group_id'), nullable=False, unique=None, default=None)
@@ -945,7 +967,8 b' class UsersGroupToPerm(Base, BaseModel):'
     __tablename__ = 'users_group_to_perm'
     __table_args__ = (
         UniqueConstraint('users_group_id', 'permission_id',),
-        {'extend_existing': True}
+        {'extend_existing': True, 'mysql_engine':'InnoDB',  
+         'mysql_charset': 'utf8'}
     )
     users_group_to_perm_id = Column("users_group_to_perm_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)
     users_group_id = Column("users_group_id", Integer(), ForeignKey('users_groups.users_group_id'), nullable=False, unique=None, default=None)
@@ -959,7 +982,8 b' class UserRepoGroupToPerm(Base, BaseMode'
     __tablename__ = 'user_repo_group_to_perm'
     __table_args__ = (
         UniqueConstraint('user_id', 'group_id', 'permission_id'),
-        {'extend_existing': True}
+        {'extend_existing': True, 'mysql_engine':'InnoDB',  
+         'mysql_charset': 'utf8'}
     )
 
     group_to_perm_id = Column("group_to_perm_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)
@@ -976,7 +1000,8 b' class UsersGroupRepoGroupToPerm(Base, Ba'
     __tablename__ = 'users_group_repo_group_to_perm'
     __table_args__ = (
         UniqueConstraint('users_group_id', 'group_id'),
-        {'extend_existing': True}
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'}
     )
 
     users_group_repo_group_to_perm_id = Column("users_group_repo_group_to_perm_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)
@@ -991,7 +1016,11 b' class UsersGroupRepoGroupToPerm(Base, Ba'
 
 class Statistics(Base, BaseModel):
     __tablename__ = 'statistics'
-    __table_args__ = (UniqueConstraint('repository_id'), {'extend_existing': True})
+    __table_args__ = (
+         UniqueConstraint('repository_id'), 
+         {'extend_existing': True, 'mysql_engine':'InnoDB',  
+          'mysql_charset': 'utf8'}
+    )
     stat_id = Column("stat_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)
     repository_id = Column("repository_id", Integer(), ForeignKey('repositories.repo_id'), nullable=False, unique=True, default=None)
     stat_on_revision = Column("stat_on_revision", Integer(), nullable=False)
@@ -1007,7 +1036,8 b' class UserFollowing(Base, BaseModel):'
     __table_args__ = (
         UniqueConstraint('user_id', 'follows_repository_id'),
         UniqueConstraint('user_id', 'follows_user_id'),
-        {'extend_existing': True}
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'}
     )
 
     user_following_id = Column("user_following_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)
@@ -1028,7 +1058,11 b' class UserFollowing(Base, BaseModel):'
 
 class CacheInvalidation(Base, BaseModel):
     __tablename__ = 'cache_invalidation'
-    __table_args__ = (UniqueConstraint('cache_key'), {'extend_existing': True})
+    __table_args__ = (
+        UniqueConstraint('cache_key'),
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'},
+    )
     cache_id = Column("cache_id", Integer(), nullable=False, unique=True, default=None, primary_key=True)
     cache_key = Column("cache_key", String(length=255, convert_unicode=False, assert_unicode=None), nullable=True, unique=None, default=None)
     cache_args = Column("cache_args", String(length=255, convert_unicode=False, assert_unicode=None), nullable=True, unique=None, default=None)
@@ -1063,7 +1097,7 b' class CacheInvalidation(Base, BaseModel)'
     @classmethod
     def get_by_key(cls, key):
         return cls.query().filter(cls.cache_key == key).scalar()
-    
+
     @classmethod
     def _get_or_create_key(cls, key, prefix, org_key):
         inv_obj = Session.query(cls).filter(cls.cache_key == key).scalar()
@@ -1074,9 +1108,9 b' class CacheInvalidation(Base, BaseModel)'
                 Session.commit()
             except Exception:
                 log.error(traceback.format_exc())
-                Session.rollback()                
+                Session.rollback()
         return inv_obj
-            
+
     @classmethod
     def invalidate(cls, key):
         """
@@ -1086,7 +1120,7 b' class CacheInvalidation(Base, BaseModel)'
 
         :param key:
         """
-        
+
         key, _prefix, _org_key = cls._get_key(key)
         inv = cls._get_or_create_key(key, _prefix, _org_key)
 
@@ -1131,7 +1165,10 b' class CacheInvalidation(Base, BaseModel)'
 
 class ChangesetComment(Base, BaseModel):
     __tablename__ = 'changeset_comments'
-    __table_args__ = ({'extend_existing': True},)
+    __table_args__ = (
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'},
+    )
     comment_id = Column('comment_id', Integer(), nullable=False, primary_key=True)
     repo_id = Column('repo_id', Integer(), ForeignKey('repositories.repo_id'), nullable=False)
     revision = Column('revision', String(40), nullable=False)
@@ -1160,7 +1197,10 b' class ChangesetComment(Base, BaseModel):'
 
 class Notification(Base, BaseModel):
     __tablename__ = 'notifications'
-    __table_args__ = ({'extend_existing': True},)
+    __table_args__ = (
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'},
+    )
 
     TYPE_CHANGESET_COMMENT = u'cs_comment'
     TYPE_MESSAGE = u'message'
@@ -1212,7 +1252,8 b' class UserNotification(Base, BaseModel):'
     __tablename__ = 'user_to_notification'
     __table_args__ = (
         UniqueConstraint('user_id', 'notification_id'),
-        {'extend_existing': True}
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'}
     )
     user_id = Column('user_id', Integer(), ForeignKey('users.user_id'), primary_key=True)
     notification_id = Column("notification_id", Integer(), ForeignKey('notifications.notification_id'), primary_key=True)
@@ -1230,7 +1271,10 b' class UserNotification(Base, BaseModel):'
 
 class DbMigrateVersion(Base, BaseModel):
     __tablename__ = 'db_migrate_version'
-    __table_args__ = {'extend_existing': True}
+    __table_args__ = (
+        {'extend_existing': True, 'mysql_engine':'InnoDB',
+         'mysql_charset': 'utf8'},
+    )
     repository_id = Column('repository_id', String(250), primary_key=True)
     repository_path = Column('repository_path', Text)
     version = Column('version', Integer)

rhodecode/model/notification.py

@@ -48,11 +48,11 b' class NotificationModel(BaseModel):'
     def __get_notification(self, notification):
         if isinstance(notification, Notification):
             return notification
-        elif isinstance(notification, int):
+        elif isinstance(notification, (int, long)):
             return Notification.get(notification)
         else:
             if notification:
-                raise Exception('notification must be int or Instance'
+                raise Exception('notification must be int, long or Instance'
                                 ' of Notification got %s' % type(notification))
 
     def create(self, created_by, subject, body, recipients=None,

rhodecode/tests/functional/test_changeset_comments.py

@@ -2,7 +2,8 b' from rhodecode.tests import *'
 from rhodecode.model.db import ChangesetComment, Notification, User, \
     UserNotification
 
-class TestChangeSetCommentrController(TestController):
+
+class TestChangeSetCommentsController(TestController):
 
     def setUp(self):
         for x in ChangesetComment.query().all():
@@ -27,7 +28,7 b' class TestChangeSetCommentrController(Te'
         rev = '27cd5cce30c96924232dffcd24178a07ffeb5dfc'
         text = u'CommentOnRevision'
 
-        params = {'text':text}
+        params = {'text': text}
         response = self.app.post(url(controller='changeset', action='comment',
                                      repo_name=HG_REPO, revision=rev),
                                      params=params)
@@ -42,13 +43,18 b' class TestChangeSetCommentrController(Te'
         self.assertTrue('''<div class="comments-number">%s '''
                         '''comment(s) (0 inline)</div>''' % 1 in response.body)
 
+        self.assertEqual(Notification.query().count(), 1)
+        self.assertEqual(ChangesetComment.query().count(), 1)
 
-        self.assertEqual(Notification.query().count(), 1)
         notification = Notification.query().all()[0]
 
-        self.assertEqual(notification.type_, Notification.TYPE_CHANGESET_COMMENT)
-        self.assertTrue((u'/vcs_test_hg/changeset/27cd5cce30c96924232df'
-                          'fcd24178a07ffeb5dfc#comment-1') in notification.subject)
+        ID = ChangesetComment.query().first().comment_id
+        self.assertEqual(notification.type_, 
+                         Notification.TYPE_CHANGESET_COMMENT)
+        sbj = (u'/vcs_test_hg/changeset/'
+               '27cd5cce30c96924232dffcd24178a07ffeb5dfc#comment-%s' % ID)
+        print "%s vs %s" % (sbj, notification.subject)
+        self.assertTrue(sbj in notification.subject)
 
     def test_create_inline(self):
         self.log_user()
@@ -57,7 +63,7 b' class TestChangeSetCommentrController(Te'
         f_path = 'vcs/web/simplevcs/views/repository.py'
         line = 'n1'
 
-        params = {'text':text, 'f_path':f_path, 'line':line}
+        params = {'text': text, 'f_path': f_path, 'line': line}
         response = self.app.post(url(controller='changeset', action='comment',
                                      repo_name=HG_REPO, revision=rev),
                                      params=params)
@@ -76,11 +82,16 b' class TestChangeSetCommentrController(Te'
                         '''repositorypy">''' in response.body)
 
         self.assertEqual(Notification.query().count(), 1)
-        notification = Notification.query().all()[0]
+        self.assertEqual(ChangesetComment.query().count(), 1)
 
-        self.assertEqual(notification.type_, Notification.TYPE_CHANGESET_COMMENT)
-        self.assertTrue((u'/vcs_test_hg/changeset/27cd5cce30c96924232df'
-                          'fcd24178a07ffeb5dfc#comment-1') in notification.subject)
+        notification = Notification.query().all()[0]
+        ID = ChangesetComment.query().first().comment_id
+        self.assertEqual(notification.type_, 
+                         Notification.TYPE_CHANGESET_COMMENT)
+        sbj = (u'/vcs_test_hg/changeset/'
+               '27cd5cce30c96924232dffcd24178a07ffeb5dfc#comment-%s' % ID)
+        print "%s vs %s" % (sbj, notification.subject)
+        self.assertTrue(sbj in notification.subject)
 
     def test_create_with_mention(self):
         self.log_user()
@@ -103,7 +114,6 b' class TestChangeSetCommentrController(Te'
         self.assertTrue('''<div class="comments-number">%s '''
                         '''comment(s) (0 inline)</div>''' % 1 in response.body)
 
-
         self.assertEqual(Notification.query().count(), 2)
         users = [x.user.username for x in UserNotification.query().all()]
 
@@ -115,7 +125,7 b' class TestChangeSetCommentrController(Te'
         rev = '27cd5cce30c96924232dffcd24178a07ffeb5dfc'
         text = u'CommentOnRevision'
 
-        params = {'text':text}
+        params = {'text': text}
         response = self.app.post(url(controller='changeset', action='comment',
                                      repo_name=HG_REPO, revision=rev),
                                      params=params)
@@ -124,7 +134,6 b' class TestChangeSetCommentrController(Te'
         self.assertEqual(len(comments), 1)
         comment_id = comments[0].comment_id
 
-
         self.app.delete(url(controller='changeset',
                                     action='delete_comment',
                                     repo_name=HG_REPO,

test.ini

@@ -206,9 +206,11 b' logview.pylons.util = #eee'
 #########################################################
 sqlalchemy.db1.url = sqlite:///%(here)s/test.db
 #sqlalchemy.db1.url = postgresql://postgres:qwe@localhost/rhodecode_tests
-#sqlalchemy.db1.echo = false
-#sqlalchemy.db1.pool_recycle = 3600
-sqlalchemy.convert_unicode = true
+#sqlalchemy.db1.url = mysql://root:qwe123qwe@localhost/rhodecode_tests
+
+sqlalchemy.db1.echo = false
+sqlalchemy.db1.pool_recycle = 3600
+sqlalchemy.db1.convert_unicode = true
 
 ################################
 ### LOGGING CONFIGURATION   ####