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")
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")
