def _buildPermissions(self, cu): # move the admin field from Permissions into UserGroups logMe(2, "Relocating the admin field from Permissions to UserGroups...") if "OldPermissions" in self.db.tables: cu.execute("drop table OldPermissions") cu.execute("create table OldPermissions as select * from Permissions") addFK = False if "UserGroupAllPermissions" in self.db.tables: self.db.dropForeignKey("UserGroupAllPermissions", name="UGAP_permissionId_fk") addFK = True cu.execute("drop table Permissions") self.db.loadSchema() schema.createUsers(self.db) cu.execute("alter table UserGroups add column " "admin INTEGER NOT NULL DEFAULT 0 ") cu.execute("select userGroupId, max(admin) from OldPermissions " "group by userGroupId") for ugid, admin in cu.fetchall(): cu.execute("update UserGroups set admin = ? where userGroupId = ?", (admin, ugid)) fields = ",".join(["userGroupId", "labelId", "itemId", "canWrite", "canRemove"]) cu.execute("insert into Permissions(%s) " "select distinct %s from OldPermissions " %(fields, fields)) cu.execute("drop table OldPermissions") if addFK: self.db.addForeignKey("UserGroupAllPermissions", "permissionId", "Permissions", "permissionId") self.db.loadSchema() return True
def _buildPermissions(self, cu): # move the admin field from Permissions into UserGroups logMe(2, "Relocating the admin field from Permissions to UserGroups...") if "OldPermissions" in self.db.tables: cu.execute("drop table OldPermissions") cu.execute("create table OldPermissions as select * from Permissions") addFK = False if "UserGroupAllPermissions" in self.db.tables: self.db.dropForeignKey("UserGroupAllPermissions", name="UGAP_permissionId_fk") addFK = True cu.execute("drop table Permissions") self.db.loadSchema() schema.createUsers(self.db) cu.execute("alter table UserGroups add column " "admin INTEGER NOT NULL DEFAULT 0 ") cu.execute("select userGroupId, max(admin) from OldPermissions " "group by userGroupId") for ugid, admin in cu.fetchall(): cu.execute("update UserGroups set admin = ? where userGroupId = ?", (admin, ugid)) fields = ",".join( ["userGroupId", "labelId", "itemId", "canWrite", "canRemove"]) cu.execute("insert into Permissions(%s) " "select distinct %s from OldPermissions " % (fields, fields)) cu.execute("drop table OldPermissions") if addFK: self.db.addForeignKey("UserGroupAllPermissions", "permissionId", "Permissions", "permissionId") self.db.loadSchema() return True
def fixPermissions(self): # because we need to add a foreign key to the Permissions and Latest # tables, it is easier if we save the tables and recreate them logMe(2, "Updating the Permissions table...") cu = self.db.cursor() # handle the case where the admin field has been relocated from Permissions cu.execute("""create table tmpPerm as select userGroupId, labelId, itemId, admin, canWrite, canRemove from Permissions""") cu.execute("drop table Permissions") self.db.loadSchema() schema.createUsers(self.db) # check if we need to preserve the admin field for a while longer cu.execute("select * from Permissions limit 0") columns = [x.lower() for x in cu.fields()] if "admin" not in columns: cu.execute("alter table Permissions add column " "admin integer not null default 0") cu.execute("""insert into Permissions (userGroupId, labelId, itemId, admin, canWrite, canRemove) select userGroupId, labelId, itemId, admin, canWrite, canRemove from tmpPerm """) cu.execute("drop table tmpPerm")