FMK2012: Datenaustausch zwischen FileMaker und MySQL von Nico Busch

FileMaker Konferenz 2012 Salzburg

www.filemaker-konferenz.com

Nicolaus Busch, N. Busch GmbHDatenaustausch mit SQL

Datenaustausch zwischen FileMaker und MySQL

Lesen, Vergleichen, Schreiben

FileMaker Konferenz2010






Struktur

1. Szenario: FileMaker-Runtime und MySQL-Server1. Beispiel MVB3

2. Beispiel Litlink

3. Lizenz beachten!

2. Was brauchen wir?1. FileMaker Advanced

2. PHP-Plugins

3. MySQL

3. Arbeiten mit FileMaker PHP am Beispiel Smartpill1. Verschiedene Arten, PHP-Code zu integrieren – Script, Feldwert,

Custom Function, externe Funktion

2. Den Function-Maker nutzen

3. Funktionen erstellen

4. Update zur Laufzeit via URL




Struktur

4. Aufbau der SQL-Datenbank

5. Den Datenabgleich organisieren1. Warum ein Transfermodul?

2. Was sind neue Daten?

3. Protokoll einrichten

4. Datenstrukturen vergleichen

5. Daten zum SQL-Server schicken

6. Daten vom SQL-Server holen

7. Konflikte lösen

8. Daten schreiben

9. Protokoll abschliessen




Szenario

MVB3: Teamsoftware für die Mütter- und Väterberatung• FileMaker Runtime• Gleichzeitige Erfassung und Bearbeitung von Daten auf diversen

Rechnern• Datennutzung soweit separat, dass in der Praxis keine Konflikte

zu erwarten sind• Datenabgleich mit MySQL-Server

Litlink: Wissensmanagement für Geisteswissenschaftler mit Web-Anwendung• FileMaker Runtime oder Vollversion• Erfassung alternierend auf verschiedenen Rechnern des gleichen

Users oder in der Weboberfläche• Projektfreigabe für andere User• Datenabgleich mit MySQL-Server




Lizenz beachten

Keine Netzwerk-Funktion für Runtime nachbauen

"All use of the Runtime and Runtime Solutions must be on a standalone basis only. You are prohibited from using the Runtime with any middleware, application server, CGI, or other software or technology that allows more than a single client to access the Runtime."




Was brauchen wir?

• FileMaker natürlich• FileMaker Advanced dringend empfohlen (Data Viewer!)• Ein PHP-Plugin• MySQL (>> XAMP)




PHP-Plugins

• Monkeybread MBS SQL Connectionhttp://www.monkeybreadsoftware.deUmfangreicher Befehlssatz

• Scodigo Smartpillwww.scodigo.comStellt Umgebung für PHP zur Verfügung

• 360works ScriptMasterwww.360works.comGroovy (Java) statt PHP

http://www.monkeybreadsoftware.de/

http://www.scodigo.com/

http://www.360works.com/




MySQL Testumgebung einrichten

Virtuelle Maschine, Website, ServerXampp verwenden: http://www.apachefriends.org/de/xampp.html

http://www.apachefriends.org/de/xampp.html




PHP-Code: Arbeitsvarianten

• Code direkt im Script• Custom Functions nutzen• Mit dem Smartpill Function Maker arbeiten




Code direkt im Script

Probleme mit AnführungszeichenFeldname und Tabellennamen in Anführung "Feldname"" im Text \" \"Feldname\"\" im Text \\\" \\\"Feldname\\\"

Feldwert in Hochkomma ID = '34523'Berechneter Wert \\\"_pk_GUID\\\" = '" & $ID & "'

Variable setzen [$table; Wert:GetValue( Get( ScriptParameter ) ; 1 )]

Variable setzen [$ID; Wert:GetValue( Get( ScriptParameter ) ; 2 )]Variable setzen [$Command;

Wert:"echo fm_sql_select(\"SELECT count(1) FROM \\\"" & $table & "\\\" WHERE \\\"_pk_GUID\\\" = '" & $ID & "'\") ;" ]

Variable setzen [$$SQL_Result; Wert:PHP_Execute ( $Command)]Aktuelles Script verlassen [$$SQL_Result]




Code in Custom Functions

• Beispiel 1: Temporären Pfad ermittelncf.GetUserTmpFolder: PHP_Execute( "echo FM_TEMPORARY_PATH;" )

• Beispiel 2: VerschlüsselungCf.Encode_Base64:

Parameter( Text, PW)PHP_Execute ( " $cipher = MCRYPT_RIJNDAEL_128; $key = hash('md5', '" & PW & "'); $iv_size = mcrypt_get_iv_size($cipher, MCRYPT_MODE_ECB); $iv = mcrypt_create_iv($iv_size, MCRYPT_RAND); $crypt_text = mcrypt_encrypt($cipher, $key, '" & Text & "', MCRYPT_MODE_ECB, $iv); echo base64_encode($crypt_text);")




Function Maker

Code ohne umständliche Anführungszeichen Package variables – Sauberer Code Kontrollierte Übergabe von Parametern Fehlerbehandlung Testumgebung




Funktionsbibliotheken erstellen• Den Function-Maker nutzen• Tests definieren• Sets erstellen• Version-Funktion nutzen




Update zur Laufzeit laden

• Laden aus lokalen DateienSetzeVar[ $Result ; Value:PHP_LoadFunctions ]

• Laden via URLSetzeVar[ $result; Value:PHP_LoadFunctionsFromURL ( "http://www.beispiel.ch/xml/PHPx_Functions.xml" ) ]




Den Datenabgleich organisieren

FileMaker-Datenbank

Transfermodul (FM) SQL




Aufbau der SQL-Datenbank

FileMaker-Datenbank

Transfermodul (FM)

SQL

In allen FM-Tabellen:s_ChanceLocal_n - Wert "1" wenn lokal geändert

In Global-Tabelle:d_LLwebLastsyncNo_ng – Nummer des letzten abgeschlossenen Synchsd_LLwebNextsyncNo_ng – Nummer des laufenden Synchsd_LLwebLastDate_tg - Zeitstempel letzter Abgleich

Tabellen enthalten immer alle Feldnamen, die in der Geschichte des Projekts existierten Import funktioniert immer mit Option "Gleiche Feldnamen"

• Gleiche Tabellen- und Feldnamen wie in FileMaker verwenden

• Zusätzliche Tabellen für Metadaten: web_global, web_synchlog

• Synchnummer: Sitzungsnummer des Datenabgleich-Vorgangs in jedem Datensatz

• Webänderung: Timestamp von Upload oder Bearbeitung im Web




Warum ein Transfermodul?

• Sicherheit• Update-Erwägungen: Aktualisierung unabhängig von

Kundendaten• Anpassungen der Daten vor dem Import & Export• Flexibilität – Import aus verschiedenen Fremdsystemen




Ablauf des Abgleichs

• Transfermodul leeren• Neue Daten lokal identifizieren• Datenset in das Transfermodul laden• Daten zum SQL-Server schicken• Transfermodul leeren• Neue Daten auf Server identifizieren• Daten vom Server holen• Daten im Transfermodul aufbereiten• Daten nach Litlink schreiben




Was sind neue Daten

• Upload: Zeitstempel letzte lokale Änderung > Letzter Abgleich UNDs_ChangeLocal_n = 1

• Download: Synchnummer SQL-Datensatz > Synchnummer letzter Abgleich ODER( Zeitstempel Webänderung > Zeitstempel letzter Abgleich UNDSynchnummer SQL-Datensatz <> Synchnummer letzter Abgleich)

"Pingpong" verhindern:Upload nur Elemente, die auf diesem Rechner bearbeitet wurdenGleichzeitiger Upload mehrerer Clients ist möglich




Rechte des Users prüfen

PHPx_SQL_CheckUserAccount:

$user = fm_get_parameter('user');$pwd = fm_get_parameter('pwd');(…)

$SQL_connect= mysql_connect($server,$account,$password); if( $SQL_connect===false) { errorHandler(SQL_ERR_COULD_NOT_CONNECT, 'Database: ' . $db); return ; }mysql_select_db($db) or die("Selection of database ".$db."

failed".mysql_error());$user = mysql_real_escape_string( $user);$pwd = mysql_real_escape_string( $pwd);$query = "SELECT count(1) as Counter FROM Adressen WHERE d_Email_t='".

$user."' and d_Passwort_t='".$pwd."' and d_active_i='1'";$res = mysql_query($query);if(!empty($res)){ $row = mysql_fetch_assoc($res); echo $row['Counter'];}




Letzte Sitzungsnummer holen…

PHPx_SQL_GetSynchNo:

mysql_select_db($db) or die("Selection of database ".$db." failed".mysql_error());

$query = "SELECT * FROM Adressen WHERE d_Email_t='".$user."' and d_Passwort_t='".$pwd."' and d_active_i='1'";

$res = mysql_query($query);$iad = 0;while($row = mysql_fetch_assoc($res)){ $litdat_ID = $row['_pk_guid']; $litdat_lastsynch = $row['d_SynchId_n']; $iad++; echo $litdat_ID." "; echo $litdat_lastsynch;

}

if( $iad!=1) { echo 'Error'; return ; }




…und neue Sitzungsnummer schreibenPHPx_SQL_SetServerSynchNo:

$sid = fm_get_parameter('sid');mysql_select_db($db) or die("Selection of database ".$db."

failed".mysql_error());$user = mysql_real_escape_string( $user);$pwd = mysql_real_escape_string( $pwd);$query = "UPDATE Adressen SET d_SynchId_n='".$sid."',d_LastUploadId_n='".

$sid."' ,d_LastUpload_t=now()WHERE d_Email_t='".$user."' AND d_Passwort_t='".$pwd."'";$res = mysql_query($query);




Die ganze Übung im FM-Script

Variable setzen [$SQL_call; Wert:PHPx_SQL_GetSynchNo ( _DGlobal::d_LLwebuser_tg; _DGlobal::d_LLwebpassword_tg; sys_Globals::s_SQLDB ; sys_Globals::s_SQLServer ; sys_Globals::s_SQLAccount ; sys_Globals::s_SQLPassword )]

Wenn [$SQL_Call = "Error"]Aktuelles Script verlassen ["Error: Konnte Synch-Nr. nicht lesen"]

SonstVariable setzen [$Result; Wert:Substitute( $SQL_call ; " " ; ¶)]Variable setzen [$$SQLUserID; Wert:GetValue( $Result ; 1)]Variable setzen [$$SQLLastSynchID; Wert:GetValue( $Result ; 2)]Feldwert setzen [_DGlobal::d_LLwebnextsyncno_ng[]; $$SQLLastSynchID+1]Schreibe Änderung Datens./Abfrage [Ohne Dialogfeld]

Ende-Wenn

Variable setzen [$SQL_call; PHPx_SQL_SetServerSynchNo ( _DGlobal::d_LLwebuser_tg; _DGlobal::d_LLwebpassword_tg; sys_Globals::s_SQLDB ; sys_Globals::s_SQLServer ; sys_Globals::s_SQLAccount ; sys_Globals::s_SQLPassword ; _DGlobal::d_LLwebnextsyncno_ng)]

Wenn [$SQL_Call = "Error"]Aktuelles Script verlassen ["Error: Konnte Synch-Nr. nicht schreiben"]

SonstAktuelles Script verlassen ["OK"]

Ende-Wenn




Datenstrukturen vergleichen

Statt hart codiertem Datenaustausch Datenbanken analysieren

1. Tabellenlisten auslesen & vergleicheno FM: Variable Setzen[ $TablesInFM ; "Autoren" & ¶ & "Periodikum" & ¶ &

"Titel" …]

o SQL: $TablesInSQL mysql_select_db($db) or die("Selection of database ".$db." failed".mysql_error()); $query = mysql_query("SHOW TABLES"); while($row = mysql_fetch_assoc($query)) { echo $row['Tables_in_'.$db]." " ; }

o Schnittmenge: [$TableList = TrText_SortLines( "-unused" ; TrText_ANDText( "-unused" ; $TablesInSQL ; $$TablesInFM )




Datenstrukturen vergleichen

2. Feldlisten auslesen & vergleichen

o Script ausführen ["fn.Smartpill: Get Fieldlist from MYSQL(List)"]o Variable setzen [$Table_sql; Wert:Get(ScriptResult)]o Script ausführen [" fn.Get Fieldlist from Transfer(List)"]o Variable setzen [$Table_fm; Wert:Get(ScriptResult)]o Wenn [$Table_sql = "Error" or $Table_fm = "Error"]o Aktuelles Script verlassen ["Error"]o Sonst

$Result = Let( [ $list = TrText_SortLines( "-unused" ; TrText_ANDText( "-unused" ; $table_sql ;

$table_fm )); $list2 = cf.LinesNotStartingWith( $list ; "x";ValueCount( $list );"¶"); $length = Length($list2) -1]; Left( $list2 ; $length))

o Aktuelles Script verlassen [$Result]o Ende (wenn)




Daten zum SQL-Server schickenVariable setzen [$Source; Wert:Get(LayoutTableName)]Schleife (Anfang)

Variable setzen [$ID; Wert:Evaluate($Source & "::_pk_guid")]

Variable setzen [$Commonfields; Wert:Let( $Fields = cf.SQLInsertCommand ( $Datafields ; "x" ; ValueCount($Datafields) );Left( $Fields; Length( $Fields )-2))]

Variable setzen [$Values; Wert:Let($Prevalues = cf.SQLInsertCommandValues ( $Datafields ; "x" ; ValueCount($Datafields)) ;"¶" & Left( $Prevalues ; Length( $Prevalues )-2))]

Variable setzen [$Result;Wert:PHPx_SQL_WriteRecordInSQL_DB ( _DGlobal::d_LLwebuser_tg; _DGlobal::d_LLwebpassword_tg; sys_Globals::s_SQLDB ; sys_Globals::s_SQLServer ; sys_Globals::s_SQLAccount ; sys_Globals::s_SQLPassword ; $$Kontext ; $ID ; $Commonfields ; $Values ; _DGlobal::d_LLwebnextsyncno_ng)]

Variable setzen [$$SQL_error; Wert:PHP_GetLastError]Gehe zu Datens./Abfrage/Seite [Nächste(r); Nach letztem beenden]

Schleife (Ende)




cf.SqlInsertCommand//Use like: SetField(Field; cf.SQLUpdateCommand ( T1; T2; ValueCount(T1) ))//Params: pT1, pSearch, NpT1

SetzeVars( [line = HoleWert(pT1; NpT1); content = Berechne( line )];

Falls( NpT1 > 1; cf.SQLInsertCommand(pT1; pSearch; NpT1-1)) & Falls( ZeichenLinks(line; Länge( pSearch )) <> psearch UND NICHT

IstLeer( content ); line & ",¶"))

"Ort¶PLZ¶Land¶Region" "Ort,¶PLZ,¶Land,¶Region,¶"




cf.SqlInsertCommandValues

//Use like: SetField(Field; cf.SQLInsertCommandValues ( T1; T2; ValueCount(T1) ))//Params: pT1, pSearch, NpT1//cf.SQLInsertCommandValues=

SetzeVars( [line=HoleWert(pT1; NpT1); Str1 = Falls( ZeichenLinks( line ;1 ) = "_" ODER ZeichenRechts(line;2) <> "_t"

ODER ZeichenLinks(line; 10)="d_datentyp" ODER $$UTFSupport <> 1 ; Berechne( line ) ; cf.CSStoFMText ( LiesAlsCSS( Berechne( line ))));

Str2 = Austauschen( Str1 ; "\\" ; "\\\\"); Content = "'" & Austauschen( Str2 ; ["'" ; "''"];["\"" ; "\\\""];["\“" ; "\\\“"];["\”" ;

"\\\”"];["’" ; "\’"];["‘" ; "\‘"];["\„";"\\\„"]) & "'"];

Falls( NpT1 > 1; cf.SQLInsertCommandValues(pT1; pSearch; NpT1-1)) & Falls( ZeichenLinks(line; Länge( pSearch )) <> psearch UND NICHT IstLeer( Str1

); content & ",¶")) D_Titel_t¶d_Ort_t¶d_Schlagworte_t (Städte mit schwierigen Namen¶北京

¶N'Djamena) 'Städte mit schwierigen Namen',¶'北京',¶'N''Djamena',




SQL_WriteRecordInSQL_DB$id ='_pk_GUID';$noempty=false; // print out only non empty fields if(isset($dbtable) && !empty($dbtable)){ $commonfields .=",x_fk_User, s_webAenderung_t, x_web_synchid"; $content .=",'".$litdat_ID."', now(),'".$websynchid."'"; $where =" WHERE _pk_GUID='". $idsel."'"; $checkres = mysql_query("select * from ".$dbtable." ".$where ); $resdel = True; if(mysql_fetch_assoc($checkres)){ // record already exists $resdel = mysql_query( "delete from ".$dbtable."".$where); if($resdel===False) echo "Failed delete: ".$idsel." "; } $update = "insert into ".$dbtable." (".$commonfields.") VALUES (".$content.")

"; if($resdel){ $updres = mysql_query($update); if( $updres === False ) { echo "Failed insert: ".$idsel." "; echo mysql_error(); } } }




Daten vom SQL-Server holen

1. Die Selection definieren…$sel = "WHERE x_fk_User='".$litdat_ID."'";if(!empty( $lastid)) $sel.=" AND x_web_synchid > '".$lastid."'";if(!empty($nextid)) $sel.=" AND x_web_synchid != '".$nextid."'";

if(!empty($litdat_datesince)){ date_default_timezone_set('Europe/Berlin'); $sel .= " AND s_webAenderung_t >'".$litdat_datesince."'";}$query ="SELECT ".$commonfields." FROM ".$dbtable." ".$sel;$res = mysql_query($query);…




Daten vom SQL-Server holen2. Werte lesen und in FM-Tabelle schreiben

while($dbrow = mysql_fetch_assoc($res)){ // loop over all records $fields = ""; $content=""; $if=0; foreach($dbrow as $fieldname => $value){

$value = str_replace("'","''",$value);if(!empty($fields)){ $fields .=","; $content .=",";}$fields .= "\"".$fieldname."\"";$content .="'".$value."'";

} $ID = $dbrow['_pk_GUID']; $ir++; $fields .=",x_IDzuAktualisieren"; $content .=",'".$ID."'"; $insert = "insert into \"TD_".$dbtable."\" (".$fields.") VALUES (".$content.") ";

$resins = fm_sql_execute($insert);

if($resins===False) {echo "Failed: ".$ID." "; echo fm_get_last_error(); echo " ";} } echo "export ".$dbtable.": ".$ir." records "; $pb = fm_close_progress_bar('web2local'); }




Konflikte lösen

• ID bekannt Vorhandenen Datensatz aktualisieren• ID unbekannt, aber Match über Namen: User fragen:

Zuordnen zu vorhandenem Datensatz, Relationen anpassen Neuer Datensatz Verwerfen




Daten schreiben

Daten aus dem Transfermodul nach Filemaker zurückschreiben Codierungen zurückrechnenDatensatz erzeugen wenn neu Datensatz updaten




FileMaker Konferenz2010

Vielen Dank unseren Sponsoren

Danke für das Bewerten dieses Vortrages

http://http./www.filemaker-magazin.de

http://www.ingeno.ch/

http://www.herrfriedrich.com/

http://www.guenther-bs.de/

http://www.monkeybreadsoftware.de/

http://www.filemaker.de/

http://www.tao-solutions.de/

Date post:	17-Nov-2014
Category:	Documents
Upload:	verein-fm-konferenz
View:	2,001 times
Download:	8 times

FMK2012: Datenaustausch zwischen FileMaker und MySQL von Nico Busch

Documents