The core__privileges_users table has the foreign key columns reversed

[David Thompson]

The core__privileges_users table consists of two foreign key columns: core__user_id and core__privilege_id. However, core__user_id contains privilege ids and core__privilege_id contains user ids! For example, running the query DELETE FROM core__privileges_users WHERE core__user_id=3; will delete all uploader privileges for all users. Bad stuff.

Here is the offending code:

class PrivilegeUserAssociation(Base):
    '''
    This table holds the many-to-many relationship between User and Privilege
    '''

    __tablename__ = 'core__privileges_users'

    privilege_id = Column(
        'core__privilege_id',
        Integer,
        ForeignKey(User.id),
        primary_key=True)
    user_id = Column(
        'core__user_id',
        Integer,
        ForeignKey(Privilege.id),
        primary_key=True)

[NattilyPidgin]

Okay I'll be fixing this today

[NattilyPidgin]

Alright, I think I've got a working solution, although the migration is a bit bulky because it seems that renaming columns isn't covered by our migration tool :/

The changes are here: ​https://gitorious.org/mediagoblin/npigeons-mediagoblin/source/7918f86ac0dd55a7be863bf29a074b4edbe4b656: in branch ticket-874

someone should review this before I push it in

[David Thompson]

Here's a direct link to the patch:

​https://gitorious.org/mediagoblin/npigeons-mediagoblin/commit/7918f86ac0dd55a7be863bf29a074b4edbe4b656

Do column names like "id_of_user" and "id_of_privilege" follow the naming conventions for foreign key columns in mediagoblin?

[NattilyPidgin]

realized I hadn't solved all the problems yet, continuing work on it. and also you're right I'll rename those columns appropriately

[NattilyPidgin]

Okay I fixed the problem (actually this time :P) and have again pushed the changes here:
​https://gitorious.org/mediagoblin/npigeons-mediagoblin/source/9adef07e8f0d169e57776bcefc03f2ae17c8920e:

[Christopher Allan Webber]

I will review this as soon as I get back from the researchcation.

[Christopher Allan Webber]

Annnnd I finally got around to it. Merged!

I also updated the code so that we use the complex code that we have to use for sqlite on sqlite only. Otherwise, do the super-simple quick rename.

Thanks, Natalie, for handling this one! You did great!