Fix broken entryids in a folder

fix-entryids.py

"""
  Fix broken entryids in a folder

  BEFORE YOU RUN THIS SCRIPT: BACKUP YOUR KOPANO DATABASE WITH mysqldump OR SIMILAR
  NOW YOU HAVE BEEN WARNED. THIS SCRIPT COMES WITH NO WARRANTY WHAT SO EVER.

  Step 1: BACKUP DATABASE! It is recommended you shutdown kopano-server while running
  this.
  Step 2: Change the USER, PASSWORD, HOST, and DB variables below
  Step 3: Find the store guid and hierarchy id of the folder

  13:25:23 bo@alpha ~ > python
  Python 2.7.13 (default, Jan 03 2017, 17:41:54) [GCC] on linux2
  Type "help", "copyright", "credits" or "license" for more information.
  >>> import kopano
  >>> store=kopano.Server().user("user1").store <--- change the username here
  >>> store.guid
  'A52FDDDAB6044C44A86E1FE2C5162342' <--- this is your storeguid

  Step 4: Find the hierarchyid of the folder where the messages with broken entryids are
  
  >>> folder=kopano.Server().user("user1").inbox <--- use .folder("folder1/folder2") for a deep hierarchy 
  >>> folder.hierarchyid
  52L <--- this is the hierarchyid (just 52 without L)

  Step 5: Run the script

  $ python fix-entryids.py <storeguid> <hierarchyid>

  The example above:

  $ python fix-entryids.py A52FDDDAB6044C44A86E1FE2C5162342 52

  Step 6: Restart kopano-server or run kopano-admin --clear-cache and the items should
  be accessible again
"""

import mysql.connector
import sys
import uuid

USER="root"
PASSWORD=""
HOST="127.0.0.1"
DB="kopano"

storeguid = sys.argv[1]
hierarchyid = int(sys.argv[2])

cnx = mysql.connector.connect(user=USER, password=PASSWORD,
                              host=HOST,
                              database=DB)

query = ("SELECT id FROM hierarchy WHERE parent=%d AND type=5" % hierarchyid)

cursor = cnx.cursor()
cursor.execute(query)

result = []
for (id,) in cursor:
  result.append(id)

for id in result:
  print "generating new entryid for: " + str(id)
  newentryid = '00000000' + storeguid + '0100000005000000' + uuid.uuid4().hex.upper() + '00000000'
  query = ("UPDATE indexedproperties SET val_binary = UNHEX('%s') WHERE tag = 4095 AND hierarchyid = %d" % (newentryid, id))
  print query
  cursor.execute(query)

cnx.commit()
cnx.close()