Sailfish OS commhistory.db to SMS Backup & Restore backup XML converter

gistfile1.txt

#!/bin/python3

# commhistorydb type:
# 2 - SMS?
# 3 - call

# direction: 1 incoming, 2 outgoing


import sqlite3
from xml.dom import minidom
import os
from time import strftime, localtime
import datetime
import pytz

con = sqlite3.connect("commhistory.db")
cur = con.cursor()

calls_db = [] # = cur.execute("select Events.direction,Events.remoteUid, events.startTime, events.endTime, events.isMissedCall from Events where events.type = 3 order by Events.startTime ASC").fetchall()

root = minidom.Document()
calls = root.createElement('calls')

cnt = 0
for call_db in calls_db:
    '''
    number - The phone number of the call.
    duration - The duration of the call in seconds.
    date - The Java date representation (including millisecond) of the time when the message was sent/received. Check out www.epochconverter.com for information on how to do the conversion from other languages to Java. "1289643415810"
    type - 1 = Incoming, 2 = Outgoing, 3 = Missed, 4 = Voicemail, 5 = Rejected, 6 = Refused List.
    presentation - caller id presentation info. 1 = Allowed, 2 = Restricted, 3 = Unknown, 4 = Payphone.
    subscription_id - Optional field that has the id of the phone subscription (SIM). On some phones these are values like 0, 1, 2  etc. based on how the phone assigns the index to the sim being used while others have the full SIM ID.
    readable_date - Optional field that has the date in a human readable format. "Nov 13, 2010 9:16:55 PM"
    contact_name - Optional field that has the name of the contact.
'''
    if call_db[1] == None:
        continue

    call = root.createElement('call')
    call.setAttribute('number', str(call_db[1]))
    call.setAttribute('duration', str(call_db[3] - call_db[2]))
    call.setAttribute('date', str(call_db[2] * 1000))
    type_ = str(call_db[0])
    if call_db[4]:
        type_ = '3'
    call.setAttribute('type', type_)
    call.setAttribute('presentation', '1')
    call.setAttribute('readable_date', datetime.datetime.fromtimestamp(call_db[3], pytz.timezone("Europe/Budapest")).strftime('%b %d, %Y %H:%M:%S %p'))
    calls.appendChild(call)
    print(call_db)
    print(datetime.datetime.fromtimestamp(call_db[3], pytz.timezone("Europe/Budapest")).strftime('%b %d, %Y %H:%M:%S %p') + " " + call_db[1] )

    cnt = cnt + 1

calls.setAttribute("count", str(cnt))
root.appendChild(calls)

xml_str = root.toprettyxml()

with open("calls.xml", "w") as f:
    f.write(xml_str)



root = minidom.Document()
smses = root.createElement('smses')

smses_db = cur.execute("select Events.direction,Events.remoteUid, events.startTime, events.freeText from Events where events.type = 2 order by Events.startTime ASC").fetchall()
cnt = 0

'''
SMS Messages:

    address - The phone number of the sender/recipient.
    date - The Java date representation (including millisecond) of the time when the message was sent/received. Check out www.epochconverter.com for information on how to do the conversion from other languages to Java.
    type - 1 = Received, 2 = Sent, 3 = Draft, 4 = Outbox, 5 = Failed, 6 = Queued
    body - The content of the message.
    read - Read Message = 1, Unread Message = 0.
    status - None = -1, Complete = 0, Pending = 32, Failed = 64.
    readable_date - Optional field that has the date in a human readable format.

    All the field values are read as-is from the underlying database and no conversion is done by the app in most cases.

              <xs:attribute name="address" type="xs:string" use="required" />
              <xs:attribute name="date" type="xs:unsignedLong" use="required" />
              <xs:attribute name="type" type="xs:unsignedByte" use="required" />
              <xs:attribute name="body" type="xs:string" use="required" />
              <xs:attribute name="read" type="xs:unsignedByte" use="required" />X
              <xs:attribute name="status" type="xs:byte" use="required" />X
              <xs:attribute name="readable_date" type="xs:string" use="optional" />X
'''


#  <sms protocol="0" address="4433221123" date="1289643415810" type="1" subject="null" body="Sample Message received by the phone" toa="null" sc_toa="null" service_center="null" read="0" status="-1" locked="0" readable_date="Nov 13, 2010 9:16:55 PM" contact_name="(Unknown)" />
for sms_db in smses_db:
    if sms_db[3] == None:
        continue

    if sms_db[3].startswith("Tisztelt Ügyfelünk! Értes") and sms_db[1] == '1270':
        continue

    if sms_db[3].startswith('OTPdirekt - OTPdirekt bejelentkezés'):
        continue

    if sms_db[3].startswith('Hitelesítés a(z)'):
        continue


    if sms_db[3].startswith('FOXPOST Csomagod érkezett'):
        continue

    if sms_db[3].endswith('Üdvözlettel: Vodafone'):
        continue

    if sms_db[3].startswith('Just found this amazing item on AliExpress'):
        continue

    print(sms_db)
    sms = root.createElement('sms')
    sms.setAttribute('address', str(sms_db[1]))
    sms.setAttribute('date', str(sms_db[2] * 1000))
    type_ = str(sms_db[0])
    sms.setAttribute('type', type_)
    sms.setAttribute('body', sms_db[3])
    sms.setAttribute('read', "1")
    sms.setAttribute('status', "0")
    sms.setAttribute('readable_date', datetime.datetime.fromtimestamp(sms_db[2], pytz.timezone("Europe/Budapest")).strftime('%b %d, %Y %H:%M:%S %p'))
    smses.appendChild(sms)
    print(datetime.datetime.fromtimestamp(sms_db[2], pytz.timezone("Europe/Budapest")).strftime('%b %d, %Y %H:%M:%S %p') + " " + sms_db[1] )
    cnt = cnt + 1

smses.setAttribute("count", str(cnt))
root.appendChild(smses)
print(cnt)

xml_str = root.toprettyxml()
with open("sms.xml", "w") as f:
    f.write(xml_str)