[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[coldsync-hackers] Problem with Sync Conduit

Hi Guys,

I am probably doing something stupid. I am getting to following in the coldsync log when I try to run my prototype sync conduit.

Inside mkpdbname("/opt/ssis/coldsync/users/mvanbeek/Device3/backup","MemoDB")
mkpdbname:    -> "/opt/ssis/coldsync/users/mvanbeek/Device3/backup/MemoDB.pdb"
Undefined subroutine &main::dlp_ReadNextModifiedRec called at /opt/ssis/coldsync/conduits/ssis-sync.pl line 192.
Undefined subroutine &main::dlp_ReadNextModifiedRec called at /opt/ssis/coldsync/conduits/ssis-sync.pl line 192.

Running the program on the command line with the -config flag shows no compiler errors, and I have checks ColdSync::SPC to check the spelling is correct.

I have "use ColdSync::SPC" at the start of the program (which is attached) and at the moment, all the program does at the moment is do the logic of finding records that have changed.

"dlp_SetSysDateTime" seems to crash the Palm, (which might be badly configured data), but doesn't cause an error message like below.

There are two copies of ColdSync::SPC on the hard drive, but both are identical (one is where the snapshot zip file was uncompressed).

I am very confused (or else I am doing something very stupid).

Any help would be appreciated,

Regards,

Marco. 
#!/usr/bin/perl

##################
# Copyright, etc #
##################


#########
# NOTES #
#########

# '$dbh' refers to the MySQl connection via DBI
# '$pdb' refers to the PDA database coonection via Coldsync
# Die() is a Coldsync function that passes the STDOUT error back to Coldsync so that it appears in the Coldsync log.

# Log levels
# ----------
# The higher the number the more detailed the logging
# 0: Application is unusable (EG: program crash)
# 1: Application is seriously flawed (EG: Unable to connect to PDA or database)
# 2: Critical conditions (Unable to write to database or PDA)
# 3: Errors (EG: Conflict resolution failed)
# 4: Warnings (EG: Conflict resolution solved)
# 5: Normal but significant (default level) (EG: Start of conduit)
# 6: informational (EG: id of each record as looped through)
# 7: Debug-level messages (EG: Full SQL code)

#################
# Start Of Code #
#################

use strict;
use ColdSync;
use ColdSync::SPC;
use Palm::StdAppInfo;
use DBI;
use Log::LogLite;

####################
# Set up Variables #
####################

my %settings;
open(CONFIG,"/etc/ssis-config");
while(<CONFIG>)
        {
        chomp;                  # no newline
        s/#.*//;                # no comments
        s/^\s+//;               # no leading whitespace
        s/\s+$//;               # no trailing whitespace
        next unless length;     # Anything left?
        my ($var, $value) = split(/\s*=\s*/,$_, 2);
        $settings{$var} = $value;
        }
close CONFIG;

my $mysql_datasource		= $settings{"mysql_datasource"};
my $mysql_username		= $settings{"mysql_username"};
my $mysql_password		= $settings{"mysql_password"};
my $mysql_palm_gluetable	= $settings{"mysql_palm_gluetable"};
my $public_user			= $settings{"public_user"};
my $Memo_user 			= $settings{"Memo_user"};
my $ToDo_user			= $settings{"ToDo_user"};
my $Address_user		= $settings{"Address_user"};
my $Datebook_user		= $settings{"Datebook_user"};

################
# Main Conduit # 
################

StartConduit("sync");


	# We need the following (for example) in the .coldsyncrc file
	#
	#	conduit sync {
	#		path: "/opt/ssis/coldsync/conduits/ssis-sync.pl";
	#		type: memo/DATA;
   	#	arguments:
  	#		ssisuser: mvanbeek;
	#		deviceID: 3
	#		logfile: /opt/ssis/coldsync/users/mvanbeek/Device3/ssis-sync-memo.log;
 	#		loglevel: 5;
	#		synctarget: Memo;
	#		}

	# Convert the headers to variables
	my $ssisuser   = $HEADERS{ssisuser};	# Current User
 	my $deviceID   = $HEADERS{deviceID};	# Current PDA as identified by Coldsync
  	my $logfile    = $HEADERS{logfile};	# Where do I save my logfile
 	my $loglevel   = $HEADERS{loglevel};	# Log level, default should be 5
 	my $synctarget = $HEADERS{synctarget};	# We might want to sync the address book more than once

	# Set up log files
	my $log = new Log::LogLite($logfile,$loglevel);
	$log->write("Start of Conduit", 5);

	my $pda_log; # Variable for PDA log (which we can only write to once, usually at the end of the conduit)


###########################
# Connect to PDA Database #
###########################

	my $dbinfo = spc_get_dbinfo() or 
		$log->write("Could not read PDA database info",1), 
		Die ("501 - Could not read PDA database info"); # YES: Coldsync Die() not Perl die()



	# Set up if variables
	my $ssis_table;
	my $pda_table;
	my $default_user;

	if ( $synctarget eq "Memo" )
		{
		use Palm::Memo;
		$ssis_table = "Memo";					# This is what we call it in the glue table
		$pda_table  = "memo";					# This is what we call it in the glue table
		if ( $Memo_user == 0 ) { $default_user = $ssisuser; }	# Is this application personal?
		else { $default_user = $public_user; }			# or public ?
		$log->write("Syncronising $dbinfo->{creator} database", 5);
		}

	my $pdb = &dlp_OpenDB($dbinfo->{name}, 0x80) or 
		$log->write("Could not open PDA database", 1), 
		Die ("501 - Could not open PDA database"); # YES: Coldsync Die() not Perl die()

#############################
# Connect to MySQL Database #
#############################

	$log->write("Connecting to MySQL",6);
	my $dbh = DBI->connect( $mysql_datasource, $mysql_username, $mysql_password ) or 
		$log->write("Could not connect to SSiS Database: $DBI::errstr", 1),
		Die ("501 - Can't connect to SSiS Database: $DBI::errstr \n"); # YES: Coldsync Die() not Perl die()

	$log->write("Locking SSiS Tables",6);
	my $sql_table_lock = $dbh->prepare("LOCK TABLES $ssis_table WRITE, Category WRITE");
	$sql_table_lock->execute() or
		$log->write("Could not lock SSiS tables: $DBI::errstr", 2),
		Die ("501 - Could not lock SSiS tables: $DBI::errstr \n"); # YES: Coldsync Die() not Perl die()
		

############
# Get Time #
############

	# Get current time
	# See http://www.perldoc.com/perl5.6/pod/func/localtime.html

	my $StartSyncTime = localtime;
	$log->write("Current System Time is $StartSyncTime", 5);

	my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
	$year += 1900; # Converts year to 4 digit
	$mon += 1; # Converts month to human numbering

	# Convert current time to PDA format
	# my $new_pda_time;
	# $new_pda_time->{year} = $year;
	# $new_pda_time->{month} = $mon;
	# $new_pda_time->{day} = $mday;
	# $new_pda_time->{hour} = $hour;
	# $new_pda_time->{minute} = $min;
	# $new_pda_time->{second} = $sec;

	#Update PDA Time
#	dlp_SetSysDateTime( $year, $mon, $mday, $hour, $min, $sec ) or 
#		$log->write("Could not set PDA time",2),
#		$pda_log .= "Could not set PDA time";
		

	# Convert current time to mysql timestamp(14) format
	$mon  =~ s/^([1-9])$/0$1/;	# Add leading zero where necessary
	$mday =~ s/^([1-9])$/0$1/;	# Add leading zero where necessary
	$sec  =~ s/^([1-9])$/0$1/;	# Add leading zero where necessary
	$min  =~ s/^([1-9])$/0$1/;	# Add leading zero where necessary
	$hour =~ s/^([1-9])$/0$1/;	# Add leading zero where necessary 

	my $CurrentSyncTimestamp = $year .$mon .$mday .$hour .$min .$sec;
	$log->write("MySQL snapshot Timestamp: $CurrentSyncTimestamp", 6);

###########################################################################

###############################
# Start PDA Dirty Record Loop #
###############################

	# Get dirty records
	foreach my $PDA_record ( dlp_ReadNextModifiedRec($pdb) )

	{
      $log->write("Next Modified record id: $PDA_record->{id}",6);

	# Set up loop variables
	my $sql_select_code;
	my $sth;
	my $glue_record;
	my $ssis_record;

	# Get matching server record
      $sql_select_code =     "SELECT ID, LastSync, SSiS_Record_ID
					FROM $mysql_palm_gluetable 
					WHERE PDA_Record_ID = '$PDA_record->{id}' # match the ID number
					AND PDA_ID = '$deviceID'			# and the PDA device number (from the colduit header)
					AND PDA_Table = '$pda_table'			# The pda table and
					AND SSiS_Table = '$ssis_table'		# ssis table from the earlier if statement
					AND Conflict = '0'				# and let's make sure it isn't a problem record.
					";

      $sth = $dbh->prepare($sql_select_code);	
      $log->write("Next modified record select code: $sql_select_code",7);

      $sth->execute() or
		$log->write("Could not run select code for PDA ID: $PDA_record->{id}: $DBI::errstr",3),
		$pda_log .= "Failed to syncronise PDA:ID:$PDA_record->{id}: $DBI::errstr",
		next;
		

	# Does the record exist in SSiS?
	if ( $sth->rows == 0 ) 
		{
		# Doesn't exist so we call the add record subroutine
		# Add_To_SSiS($PDA_record->{id},$synctarget);
		next;
		}

	# Are there too many records the glue table?
	if ( $sth->rows > 1 ) 
		{
		$log->write("Too many matches for for PDA ID: $PDA_record->{id}",3);
		$pda_log .= "Too many matches for PDA ID: $PDA_record->{id}";
		next;
		}

	# If the record exists, check PDA record for {deleted} flag
	if ( $PDA_record->{attributes}{deleted} )
		{
		# If exists check for {archive} flag
		if ( $PDA_record->{attributes}{archive} )
			{
			# If it does, mark as archived
			# Delete_From_PDA($PDA_record->{id},$synctarget,0)
			next;
			}
		else
			{	
			# Set as Archive in glue table and  set Deleted flag 
			# Delete_From_PDA($PDA_record->{id},$synctarget,1)
			next;
			}
		}

	# Get server record timestamp
	my $glue_record = $sth->fetchall_arrayref( { ID => 1, LastSync => 1, SSiS_Record_ID => } );

	$sql_select_code =     "SELECT LastChange 
					FROM $ssis_table
					WHERE ID = '$glue_record->{SSiS_Record_ID}'	# Matching the correct id number
					AND	(
						User = '$ssisuser'			# 'my' records or ...
						OR User = '$public_user'		# Everybody's records
						)
					AND Deleted = '0'				# Hasn't been deleted
							
					";

      $sth = $dbh->prepare($sql_select_code);	
      $log->write("Getting ssis record ID: $glue_record->{SSiS_Record_ID}: $sql_select_code",7);

      $sth->execute() or
		$log->write("Could not run select code for SSiS ID: $glue_record->{SSiS_Record_ID}: $DBI::errstr",3),
		$pda_log .= "Failed to syncronise SSiS:ID:$glue_record->{SSiS_Record_ID}: $DBI::errstr",
		next;
		
	$ssis_record = $sth->fetchall_arrayref( { LastChange => 1 } );

	# If server record older than last sync timestamp, check server record deleted flag 

	if ( $ssis_record->{LastChange} > $glue_record->{LastSync} )
		{
		# Mark for conflict resolution
		$log->write("Marking SSiS:ID:$glue_record->{ID}  for conflict resolution",6);
		# Mark_As_Conflict($glue_record->{ID});
		next;
		}
	else 		# Server record hasn't been changed since last sync, so update it
		{
		# call update record subroutine
		# Update_SSiS($glue_record->{ID},$synctarget);
		next;
		}

	# End "foreach $PDA_record" Loop
	}

################################
# End of PDA dirty record loop #
################################

###########################################################################

##################################
# Start Server dirty record loop #
##################################

	# First we need to find all SSiS records that do not have a glue table entry. 
	# This could be dangerous for duplicates so we must do a lookup in the PDA 
	# before any new records are added. If necessary we mark them as conflicted to be resolved later.
	# We could eventually do this as a 'fetch' if speed becomes an issue.

	my $sql_select_code  = "SELECT $ssis_table.ID, $mysql_palm_gluetable.ID 
					FROM $ssis_table LEFT JOIN $mysql_palm_gluetable ON $ssis_table.ID = $mysql_palm_gluetable.SSiS_Record_ID 
					WHERE $mysql_palm_gluetable.ID IS NULL";
	
	my $sth = $dbh->prepare($sql_select_code);	
        $log->write("Getting unsync'd ssis records: $sql_select_code",7);

        $sth->execute() or
		$log->write("Could not run select code for unsync'd SSiS records: $DBI::errstr",3),
		Die ("501 - Can't connect to select SSiS records: $DBI::errstr \n"); # YES: Coldsync Die() not Perl die()
		
	my $ssis_unsynced = $sth->fetchall_arrayref( { SSiS_Record_ID => 1 , Glue_table_ID => 1 } );

	foreach my $new_ssis_record ( @$ssis_unsynced )
		{
		# Add to glue table and add to PDA
      		$log->write("Adding ssis record:ID: $new_ssis_record->{SSiS_Record_ID} to PDA",6);
		# Add_To_PDA($new_ssis_record->{SSiS_Record_ID},$synctarget);
		}

	# Get all dirty server records (by using timestamps)

	$sql_select_code =  "SELECT $mysql_palm_gluetable.ID, $mysql_palm_gluetable.PDA_Record_ID, $mysql_palm_gluetable.SSiS_Record_ID
					FROM $mysql_palm_gluetable, $ssis_table
					WHERE $mysql_palm_gluetable.SSiS_Record_ID = $ssis_table.ID	# Join by ID numbers
					AND $mysql_palm_gluetable.Archive = '0'				# Ignore archived records
					AND $ssis_table.Deleted = '0'						# Ignore deleted records
					AND $mysql_palm_gluetable.PDA_ID = '$deviceID'			# Match the right PDA device
					AND $mysql_palm_gluetable.PDA_Table = '$ssis_table'		# Match the right ssis table
					AND $mysql_palm_gluetable.SSiS_Table = '$pda_table'		# Match the rigth PDA database
					AND $mysql_palm_gluetable.Conflict = '0'				# Ignore conflicted records
					AND $ssis_table.LastChange < '$CurrentSyncTimestamp'	 	# Older than when we started
					AND $ssis_table.LastChange > $mysql_palm_gluetable.LastSync # Newer than the last sync
					AND   (
						$ssis_table.User = '$ssisuser'				# 'my' records or ...
						OR  $ssis_table.User = '$public_user'			# Everybody's records
						)
					";

      my $sth = $dbh->prepare($sql_select_code);	
      $log->write("Getting dirty ssis records: $sql_select_code",6);

      $sth->execute() or
		$log->write("Could not run select code for SSiS records: $DBI::errstr",3),
		Die ("501 - Can't connect to select SSiS records: $DBI::errstr \n"); # YES: Coldsync Die() not Perl die()
		

	my $ssis_record = $sth->fetchall_arrayref( { ID => 1 , PDA_Record_ID => 1 , SSiS_Record_ID => 1 } );

	foreach my $ssis_record_row ( @$ssis_record )
		{
            $log->write("Looking for Palm record match: $ssis_record_row->{PDA_Record_ID}",6);

		# Get PDA record
		my $PDA_record = dlp_ReadRecordByID ( $pdb, $ssis_record_row->{PDA_Record_ID} ) or 
			$log->write("Could not retrieve PDA:ID:$ssis_record_row->{PDA_Record_ID}",4),
			next;

		# If exists check dirty flag (shouldn't be any left at this point)
		if ( $PDA_record->{attributes}{dirty} )
			{
			# Mark for conflict resolution
			$pda_log .= "Failed to syncronise SSiS:ID:$ssis_record_row->{SSiS_Record_ID}";
	            $log->write("Could not syncronise SSiS:ID:$ssis_record_row->{SSiS_Record_ID} to PDA",6);
			# Mark_As_Conflict($glue_record->{ID});
			next;
			}
		else
			{
			# Update PDA
	           	$log->write("Updating SSiS:ID:$ssis_record_row->{SSiS_Record_ID} to PDA",6);
			# Update_PDA($glue_record->{ID}, $synctarget);
			next;
			}

		# End '@$ssis_record' foreach loop
		}

###################################
# End of Server dirty record loop #
###################################

###########################################################################

#####################################
# Start of Conflict resolution Loop #
#####################################

	# Get list of conflicts

	# Reset failure flag
	# Reset overwrite flag

	# Get PDA record and Server record

		# Match each field (do a while on a foreach ??)

			# If same next field

			# If different check contents

				# If server field is empty check overwrite flag

					# If set overwrite with PDA data

				# If PDA field is empty check overwrite flag

					# If set overwrite with Server data

				# If both fields contain data set failure flag

			# Next field

			# Check for overwrite flag

				# If set reset conflict resolution flag and get next record

				# If not set check failure flag

					# If not set, set overwrite flag and loop through fields again

					# If set, send entry to log and next record

				# End If

			# End If

		# End Loop

	# End Loop

#########################################
# End of Conflict resolution subroutine #
#########################################


#########################################
# Unlock and close database connections #
#########################################

if ( $pda_log eq "" ) { $pda_log = "$dbinfo->{name}: OK"; } 
DlpAddSyncLogEntry($pdb, $pda_log) or $log->write("Could not write PDA log",2);


&dlp_CloseDB($pdb) or $log->write("Could not close PDA Database",1);

my $sql_table_unlock = $dbh->prepare("UNLOCK TABLES");

$sql_table_unlock->execute() or
	$log-write("Could not unlock MyQL tables",1),
	Die ("501 - Could not unlock MySQL tables. \n"); # YES: Coldsync Die() not Perl die()
	
$dbh->disconnect or $log->write("Could not close PDA connection",1);

$log->write("Unlocked tables and Disconnected: End of conduit",5);

EndConduit;

##################
# End of conduit #
##################


####################
# Helper functions #
####################

# TO DO & memory joggers
# When add records we need to remember to check deleted and archive flag durying the add subroutine
# when adding & modifing records, we should force the timestamp to our preset value
# We have an issue with the all_user and when switching users in the records.
# public_user and new record defaults apply here...
# neeed to check log entries are before subroutine code calls

# Add_To_SSiS()		# Needs to create glue table, & check for existance. If they already exist, mark as conflict.
# Delete_From_PDA()	# Set Archive flag in glue table/ Mark as deleted & Delete record from PDA
# Mark_As_Conflict()
# Update_SSiS()
# Add_To_PDA()
# Update_PDA()