Sailfish_Contacts_Restore/ContactRestore.py

#!/usr/bin/python3
# coding=utf-8
# Doc
#  Script to extract Contacts from Sailfish Contact SQLite DB located at 
#
#  
# Links
#  Projects Page: https://wiki.siningsoft.de/doku.php?id=sailfishos:projects:sailfish_contacts_rescue
#  FileFormatdescription: https://docs.fileformat.com/email/vcf/#vcf-30-example
#  Pytho vobject: http://eventable.github.io/vobject/

# Version
version=0.6

# ChangeLog
#  2021-08-03 - 0.1 - multiple E-Mails with different types are working correctly
#  2021-08-09 - 0.2 - Phonenumbers with parameters, Addresses with parameters, E-Mail-Addresses with marameters
#  2021-08-10 - 0.3 - load Avatars into VCards
#  2021-08-28 - 0.4 - bugfixing after full exporting my contacts
#                   - Address export fixing
#  2021-09-18 - 0.5 - bugfixing wrong table column mapping
#  2021-09-21 - 0.6 - changing table mapping, adding fix for phonenumber categories

import sqlite3
import vobject
import uuid
import argparse
import os

#class vobject2(vobject):



def DEBUG(debug,msg):
    if debug is True:
        print("..DEBUG: " + msg)

# will return a default value, when var is None
def setDefault(var, default):
    if var is None:
        return default
    else:
        return var

parser = argparse.ArgumentParser(description='Restore SailfishOS 3 Contacts', epilog='This script was written to restore SailfishOS 3 contacts as VCF files. To see additional information, visit: https://wiki.siningsoft.de/doku.php?id=sailfishos:projects:sailfish_contacts_rescue' )
parser.add_argument('--db','-d', required=True, help="Sqlite3 Database file usually /home/{nemo,defaultuser}/.local/share/system/Contacts/qtcontacts-sqlite/contacts.db")
parser.add_argument('--output','-o',required=True, help="Output directory for vcf files")
parser.add_argument('--avatars','-a',required=False, help='Avatar directory. If present otherwise we skip this block of avatars, means, no avatars at all')
parser.add_argument('--debug',action="store_true",help="debugging output to identify problems")
parser.add_argument('--version', action='version', version='%(prog)s ' + str(version))
args = parser.parse_args()



SQLconn = sqlite3.connect(args.db)
SQLconn.row_factory = sqlite3.Row #das macht den Unterschied und liefert die Werte

try:
    SQLContCur = SQLconn.cursor()
    SQLContCur.execute('SELECT * FROM Contacts')
    rows = SQLContCur.fetchall()
    
    for row in rows:
        

            # contactID abfragen
            contactID=setDefault(row['contactId'],"NA")
            familyN=setDefault(row['lastName'],"NA")
            givenN=setDefault(row['firstName'],"NA")
            
            if familyN != None and givenN != None:
                fullN = givenN + " " + familyN
            
            #fullN=setDefault(row['displayLabel'],"NA")
            cardfile=args.output + "/" + fullN.replace(" ","_").replace("(","").replace(")","").replace(".","").replace(",","") + ".vcf"
            
            
            # wir erstellen das Objekt
            vcf = vobject.vCard()
            
            vcf.add('uid').value = str(uuid.uuid4())
            #vcf.add('uid').value = "Testdaten"
            vcf.add('n').value = vobject.vcard.Name( family=familyN, given=givenN )
            vcf.add('fn').value =fullN
            
            
            print("exporting " + fullN + " to file " + cardfile)
            DEBUG(args.debug,"Contact " + str(fullN) + " family-name=" + str(familyN) + " given-name=" + str(givenN))
            
            # abfrage der Adressdaten
            SQLADRCur = SQLconn.cursor()
            SQLADRCur.execute('SELECT * FROM Addresses JOIN Details on Addresses.detailId = Details.detailId where Addresses.contactId = ' + str(contactID)) 
            ADRrows = SQLADRCur.fetchall()
    
            for ADRrow in ADRrows:
            
                if ADRrow['street'] is not None:
                    ADRstr=str(ADRrow['street'])
                else:
                    ADRstr="NA"

                if ADRrow['locality'] is not None:
                    ADRcit=str(ADRrow['locality'])
                else:
                    ADRcit="NA"
                                    
                if ADRrow['region'] is not None:
                    ADRreg=str(ADRrow['region'])
                else:
                    ADRreg="NA"

                if ADRrow['postCode'] is not None:
                    ADRcod=str(ADRrow['postCode'])
                else:
                    ADRcod="NA"

                if ADRrow['country'] is not None:
                    ADRcou=str(ADRrow['country'])
                else:
                    ADRcou="NA"

                # we need to know the context we've read here
                # 2021-09-19 - it figured out, that this might cause a call of AddressBehaviour for Address.replace(***)
                #if ADRrow[14] is not None:
                #    addADR="ADR;TYPE="+str(ADRrow[14])
                #else:
                #    addADR="ADR"

                vcf.add("ADR").value = vobject.vcard.Address(street=ADRstr, city=ADRcit, region=ADRreg, code=ADRcod,country=ADRcou)
                #adr = vcf.add(addADR).value = vobject.vcard.Address()

                DEBUG(args.debug,"Addressdata: street="  + ADRstr + " city=" + ADRcit + " region=" + ADRreg + " code=" + ADRcod + " country=" + ADRcou + " context=" + str(ADRrow[14]))
            
            ## Abfragen Organisation
            SQLORGCur = SQLconn.cursor()
            SQLORGCur.execute('SELECT * from Organizations where contactId = ' + str(contactID))
            ORGrows = SQLContCur.fetchall()
            for ORGrow in ORGrows:
           
                org = vcf.add('ORG').value =  [str(ORGrow['name']), str(ORGrow['department'])]
                
                if ORGrow[4] is not None:
                    title = vcf.add('TITLE').value = str(ORGrow['title'])
                
                if ORGrow[3] is not None:
                    role = vcf.add('ROLE').value = str(ORGrow['role'])
                
                # Also parameters are possible. Could be read out
                # | columnID | column        |
                # ----------------------------
                # |     0    | detailId	     |
                # |     1    | contactId     |
                # |     2    | name	         |
                # |     3    | role	         |
                # |     4    | title         |
                # |     5    | location	     |
                # |     6    | department    |
                # |     7    | logoUrl       |
                # |     8    | assistantName |

            
            ## Abfragen E-Mail-Adressen
            SQLEmailCur = SQLconn.cursor()
            SQLEmailCur.execute('SELECT * from EmailAddresses JOIN Details on Details.detailId= EmailAddresses.detailId where EmailAddresses.contactId = ' + str(contactID))
            Emailrows = SQLEmailCur.fetchall()
            for Emailrow in Emailrows:
                
                # debug ausgabe
                DEBUG(args.debug,str(Emailrow[2]) + " at " + str(Emailrow[9]))
                
                email = vcf.add('email')
                email.value = str(Emailrow['emailAddress'])
    
                # nur den Typ einpflegen, wenn das hier nicht none ist
                if Emailrow['contexts'] != None:
                    email.type_param = str(Emailrow['contexts'])


            SQLPhoneCur = SQLconn.cursor()
            SQLPhoneCur.execute('SELECT * from PhoneNumbers JOIN Details on Details.detailId = PhoneNumbers.detailId where PhoneNumbers.contactId = ' + str(contactID))
            Phonerows = SQLPhoneCur.fetchall()
            ## Abfragen Telefonnummer, Fax, SMS - Nummern kommen aus der gleichen Tabelle
            for Phonerow in Phonerows:
                
                # wir müssen die SubTypen unterscheiden
                #Null	voice
                #1	cell
                #2	fax
                #3	pager
                #6	video
                #10	Assistent
                
                
                
                # None is a normal phone Number


                if Phonerow['subTypes'] == "1":
                    phcat='cell'
                elif Phonerow['subTypes'] == "2":
                    phcat='fax'
                elif Phonerow['subTypes'] == "3":
                    phcat='pager'
                elif Phonerow['subTypes'] == "6":
                    phcat='video'
                elif Phonerow['subTypes'] == "10":
                    phcat='assistent'
                elif Phonerow['subTypes'] is None:
                    phcat='voice'
                    
                
                # debug ausgabe
                DEBUG(args.debug,str(Phonerow['phoneNumber']) + " at " + str(Phonerow['contexts']) + " as subtype=" + str(Phonerow['subTypes']) + "=" + phcat)

                phaddcat = 'TEL;TYPE='+phcat;

                # nur den Typ einpflegen, wenn das hier nicht none ist
                if Phonerow['contexts'] != None:
                    phaddcat = phaddcat + "," + Phonerow['contexts']

                phone = vcf.add(phaddcat).value = str(Phonerow['phoneNumber'])
                
                # nur den Typ einpflegen, wenn das hier nicht none ist
                if Phonerow['contexts'] != None:
                    try:
                        phone.type_param = str(Phonerow['contexts'])
                    except AttributeError:
                        continue


            if args.avatars is not None:
                DEBUG(args.debug,"Avatar Argument given")
                SQLAVTRCur = SQLconn.cursor()
                SQLAVTRCur.execute('SELECT imageURL from Avatars where contactId = ' + str(contactID))
                AVTRrows = SQLAVTRCur.fetchall()
                
                ## get Avatar Filelink from DB
                for AVTRrow in AVTRrows:
                    DEBUG(args.debug,"found PHOTO entry")
    
    
                    if AVTRrow[0] is not None:
                        avatarfile=os.path.split(AVTRrow[0])[1]

                        # pre-checks
                        #  - is it a file
                        #  - is it jpg

                        import mimetypes
                        afile=args.avatars + "/" + avatarfile
                        
                        DEBUG(args.debug,"Avatar File: " + afile + " mimetype: " + str(mimetypes.guess_type(afile)))
                        if os.path.isfile(afile) and mimetypes.guess_type(afile)[0] == "image/jpeg":
                            DEBUG(args.debug,"found file " + afile)
                            import base64
                            # actions
                            #  - encode base24 to variable
                            #  - add to vcard
                            fileopen=open(afile,'rb')
                            bfile=base64.b64encode(fileopen.read())
                            fileopen.close()

                            # https://stackoverflow.com/a/61532783
                            photo = vcf.add('PHOTO;ENCODING=b;TYPE=image/jpeg')
                            photo.value = str(bfile.decode('utf-8'))


                        else:
                            print("file " + afile + " not found or no JPG")                    
                        
            # Output to file
            f = open(cardfile,'w')
            f.write(vcf.serialize())
            f.close()

# hier brauchen wir einige eception handles -> wie bekommen wir die einzelnen exceptions heruas ?
#except:
    #print("Error in executing SQL")
    
    
except AttributeError:
    print("Datatype mismatch")
    raise

# das generöse Except am Ende    
except:
    print("unhandled error")
    raise