Aufräumarbeiten im CardDAV für Davical und Owncloud

Für unsere geschäftlichen Kontakte setzen wir Davical als CardDAV-Dienst ein. Damit lassen sich Kontakte auf vielen verschiedenen Geräten aber auch bei verschiedenen Mitarbeitern sehr einfach synchron halten. Für private Daten wird vom ein oder anderen Mitarbeiter auch Owncloud verwendet. Hierbei bleiben Aufräumarbeiten am Datenbestand leider nicht aus.

Durch einen Bug in der Systemsoftware unserer Blackberry-Smartphones kommt es häufig zu Synchronisationsfehlern bei unseren CardDAV-Diensten. Hier tritt der Fehler auf, dass bei der Anlage eines neuen Kontaktes ein Kontakt mehrfach (bis zu 2.000 mal) auf dem CardDAV-Server angelegt wird. Zu beobachten ist, dass es anscheinend eine Aufschaukelung bei der Synchronisation besteht: Das Blackberry sendet den neuen Kontakt zum CardDAV-Dienst, dieser meldet dem Smartphone eine positive Anlage des Kontaktes zurück. Dies wird vom Blackberry nicht korrekt interpretiert und die Anlage des Kontaktes wird erneut gestartet. Dies konnten wir an den verschiedenen Anlage-Zeitstempeln der Kontakte festmachen, zwischen denen jeweils ein paar Sekunden lagen.

Da Blackberry diesen Fehler bislang noch nicht gepatched hat, entwickelten wir zwei Skripte, die diese Daten-Doubletten erkennen und entfernen können.

Aufgrund der Datenstruktur von Davical reicht hier der folgende SQL-Befehl, der die Datenbasis bereinigt und Doubletten entfernt:

davical-cleanup.sql

CREATE TEMPORARY TABLE all_dav_ids AS (
  SELECT dav_id FROM (
    SELECT a.dav_id,
    ROW_NUMBER() OVER(PARTITION BY 
      a.version,a.nickname,a.fn,a.n,a.note,a.org,a.url,a.fburl,a.caladruri,a.caluri,
      b.type,b.box_no,b.unit_no,b.street_address,b.locality,b.region,b.postcode,b.country,b.property,
      c.type,c.email,c.property,
      d.type,d.tel,d.property
      ORDER BY a.dav_id ASC
    ) AS Row
    FROM addressbook_resource a
    LEFT JOIN addressbook_address_adr b ON a.dav_id=b.dav_id
    LEFT JOIN addressbook_address_email c ON a.dav_id=c.dav_id
    LEFT JOIN addressbook_address_tel d ON a.dav_id=d.dav_id
  ) dups
  WHERE 
  dups.Row > 1 ORDER BY dav_id
);
 
DELETE FROM caldav_data WHERE dav_id IN (SELECT dav_id FROM all_dav_ids);
DELETE FROM addressbook_address_tel WHERE dav_id IN (SELECT dav_id FROM all_dav_ids);
DELETE FROM addressbook_address_email WHERE dav_id IN (SELECT dav_id FROM all_dav_ids);
DELETE FROM addressbook_address_adr WHERE dav_id IN (SELECT dav_id FROM all_dav_ids);
DELETE FROM addressbook_resource WHERE dav_id IN (SELECT dav_id FROM all_dav_ids);

Die Entfernung der Doubletten in Owncloud gestaltete sich etwas schwerer, da die Datenbankstruktur etwas allgemeiner und mit Key-Value-Paaren aufgebaut ist. Für uns war es hierbei praktisch, einen Webdienst starten zu können. Daher ist die folgende Lösung in PHP geschrieben:

owncloud-cleanup.php

<?php
 
class MySQL_Connector{
	private $cid;
 
	public function __construct($host,$user,$password,$name){
		$this->cid = mysqli_connect($host, $user, $password, $name);
		if(!$this->cid)
			self::error();
		$this->query("SET NAMES UTF8");
	}
 
	public function query($query){
		return mysqli_query($this->cid, $query);
	}
 
	public function queryArr($query){
		return $this->getArrFromQuery($this->query($query));
	}
 
	public function queryFetch($query){
		return $this->fetch_array($this->query($query));
	}
 
	public function fetch_array($fetch_array){
		return mysqli_fetch_array($fetch_array);
	}
 
	public function escape($escape_string){
		if(!is_null($escape_string)){
			return mysqli_real_escape_string($this->cid, $escape_string);
		}else{
			return null;
		}
	}
 
	private function getArrFromQuery($dbreq){
		$i=0;
		$result=array();
		while ($dbres = $this->fetch_array($dbreq)) {
			foreach (array_keys($dbres) as $col) {
				$result[$i][$col] = $dbres[$col];
			}
			$i++;
		}
		return $result;
	}
 
	private function error(){
		if (mysqli_errno($this->cid) != 0){
			die("<br />\n<b>MySQL error</b>:  ".mysqli_errno($this->cid).": ".mysqli_error($this->cid)."<br />\n");
		}
	}
 
	public function disconnect(){
		return mysqli_close($this->cid);
	}
}
 
define("MYSQL_HOST", "localhost");
define("MYSQL_USER", "");
define("MYSQL_PASS", "");
define("MYSQL_NAME", "owncloud");
 
$db = new MySQL_Connector(MYSQL_HOST,MYSQL_USER,MYSQL_PASS,MYSQL_NAME);
 
$fullDB = array();
$shouldDeletedIDs = array();
 
// Get all double contacts, separated in address-books
$q = $db->query("
	SELECT
		b.id,
		b.addressbookid,
		b.fullname
	FROM
		(
			SELECT
				x.addressbookid,
				x.fullname
			FROM
				(
					SELECT
						COUNT(*) AS cnt,
						fullname,
						addressbookid
					FROM
						oc_contacts_cards
					GROUP BY
						addressbookid, fullname
				) x
			WHERE
				x.cnt > 1
		) a
		RIGHT JOIN oc_contacts_cards b ON a.addressbookid=b.addressbookid
	WHERE
		a.fullname = b.fullname
	ORDER BY
		b.addressbookid ASC,
		b.fullname ASC,
		b.id ASC
	");
 
/* Build up internal database with all possible contacts
 *
 * $fullDB[<AddressBookID>][<FullName>][
 *   idList -> List of IDs for this full-name
 *   card -> List of all contact-cards
 * ]
 */
while($elem = $db->fetch_array($q)){
	if(empty($elem['fullname']))
		continue;
 
	if(!isset($fullDB[$elem['addressbookid']])){
		$fullDB[$elem['addressbookid']] = array(
			$elem['fullname'] => array(
				"idList" => array(),
				"card" => array()
			)
		);
	}
	if(!isset($fullDB[$elem['addressbookid']][$elem['fullname']])){
		$fullDB[$elem['addressbookid']][$elem['fullname']] = array(
			"idList" => array(),
			"card" => array()
		);
	}
	if(!in_array($elem['id'],$fullDB[$elem['addressbookid']][$elem['fullname']]['idList'])){
		$fullDB[$elem['addressbookid']][$elem['fullname']]['idList'][] = $elem['id'];
	}
}
 
// Build up list of contact-cards and selecting ids, who can be deleted
foreach($fullDB as $addressbook=>$names){
	foreach($names as $name=>$nameData){
		foreach($nameData['idList'] as $id){
			$q = $db->query("
				SELECT
					*
				FROM
					oc_contacts_cards_properties
				WHERE
					contactid = ".$id."
			");
 
			// Get the actual contact-card, except the UID-Field
			$card = array();
			while($elem = $db->fetch_array($q)){
				if($elem['name']=="UID")
					continue;
 
				$card[$elem['name']] = $elem['value'];
			}
 
			// If no contact-card exist, add them to the local DB and continue
			if($nameData['card'] == array()){
				foreach($card as $cardKey=>$cardValue){
					$nameData['card'][0][$cardKey] = $cardValue;
				}
				continue;
			}
 
			// Check, if the actual contact-card exist for this contact
			$cardAlreadyExist = false;
			$tmpNameData = array();
			for($i=0;$i<count($nameData['card']);$i++){
				$curCardAlreadyExist = true;
				if(count($card)==count($nameData['card'][$i])){
					foreach($nameData['card'][$i] as $cardKey=>$cardValue){
						if(!isset($card[$cardKey]) || $card[$cardKey]!=$cardValue){
							$curCardAlreadyExist = false;
						}
					}
				}else{
					$curCardAlreadyExist = false;
				}
 
				if($curCardAlreadyExist){
					$cardAlreadyExist = true;
					break;
				}
			}
 
			// If exist, delete. If not, add it to the local DB
			if($cardAlreadyExist){
				$shouldDeletedIDs[] = $id;
			}else{
				$arr = array();
				foreach($card as $cardKey=>$cardValue){
					$arr[$cardKey] = $cardValue;
				}
				$nameData['card'][] = $arr;
			}
		}
	}
}
 
$cnt = count($shouldDeletedIDs);
$lst = implode(',',$shouldDeletedIDs);
$db->query("DELETE FROM oc_contacts_cards WHERE id IN (".$lst.")");
$db->query("DELETE FROM oc_contacts_cards_properties WHERE contactid IN (".$lst.")");
echo "Es wurden ".$cnt." doppelte Kontakte gelöscht.";