Author: Fabrice

[vc_row bg_color=””][vc_column][vc_column_text]Après avoir passé deux jours sur l’écriture d’une fonction de calcul, je me suis dit que je n’étais plus à un jour près et que raconter l’histoire de cette écriture pourrait être intéressant pour d’autres développeurs, non pas tellement pour cette fonction en elle-même, mais pour capter l’état d’esprit et les idées générales qui permettent d’aborder une fonction complexe.

En plus, Claris m’ayant fait l’honneur de me programmer à la conférence Engage en mars prochain à Austin, avec un sujet aussi peu banal et prétentieux que “comment, moi, Fabrice Nordmann, j’aborde une problématique à résoudre avec la plateforme FileMaker”, il faut que je commence sérieusement à faire de l’introspection pour comprendre moi-même comment je fonctionne afin de l’expliquer. En tant que lecteur vous me servez donc de cobaye, ce dont je vous remercie.[/vc_column_text][vc_column_text]

Tout d’abord, expliquons ce que fait la fonction.

La fonction s’appelle json2sql et permet de formuler une requête SQL pour FileMaker en utilisant un paramètre JSON.

En effet, la fonction d’écriture de JSON, JSONSetElement, est devenue franchement agréable à utiliser avec la version 21 de FileMaker, et on prend vite l’habitude, alors qu’écrire du SQL est vraiment pénible, surtout si on veut l’écrire bien, sans dépendance au nom des champs, afin que la requête ne s’arrête pas de fonctionner si on renomme une rubrique.

L’idée est donc de passer un paramètre JSON à cette fonction, qu’elle le traduise en requête SQL et l’exécute (ça c’est l’idée avec laquelle j’ai commencé, mais ça s’est bien compliqué ensuite, vous allez voir).

Nous avions déjà une fonction que nous utilisons beaucoup, sql.match ( _requestedField ; _matchField ; _match ), qui permet de retrouver les valeurs d’une colonne (_requestedField) dans les enregistrements correspondant à un critère dans une requête du type.

SELECT _requestedField WHERE _matchField = _match

Elle nous permet, de réaliser une grosse proportion des requêtes dont nous avons besoin quand on code en FileMaker.

Mais évidemment, c’est très limité. Si on veut plusieurs colonnes, des fonctions, plusieurs comparaisons (WHERE)… ça ne suffit pas.

Nous avons aussi un ensemble de fonctions qui nous aident à écrire proprement du SQL, comme sql.table, sql.col, sql.in.clause… mais rien d’aussi cool que le JSON.

Et puis aussi, il y avait de la vaisselle sale dans l’évier, donc vraiment c’était le moment de trouver une fonction à écrire.

Donc c’est parti, “yapuka”

D’abord, quelle doit être “l’empreinte” de cette fonction ? Quel nom ? quels paramètres ?

J’aime faire simple, alors :

json2sql ( _json )

Ben oui, JSON, c’est déjà fait pour structurer l’information, donc je vais tout indiquer dans le JSON, c’est joli. (Spoiler : ça ne finira pas comme ça).[/vc_column_text][vc_column_text]Je réfléchis donc à la structure du JSON, en ayant bien en tête que la fonction doit, in fine, appeler la fonction native

ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )

Si vous n’êtes pas familier avec la fonction ExecuteSQL, rappelons que sqlQuery est une requête SQL de type SELECT uniquement, que l’on peut utiliser des ? en lieu et place des valeurs de comparaison et que ces ? vont être remplacés par FileMaker par des éléments dans la liste d’arguments passés en fin de fonction, dans l’ordre. L’avantage de cela est que FileMaker gère tout seul les types de données (transforme les dates au format SQL, ajoute les guillemets autour des textes mais pas des nombres, gère le séparateur décimal…)

Donc par exemple si j’écris :

ExecuteSQL ( "SELECT primaryKey FROM invoices WHERE clientName > ? AND invoiceDate = ? AND totalAmount > ?" ; "" ; "" ; "L" ; Date ( 6 ; 1 ; 2024 ) ; round ( 100/3 ;2 ) )

Alors FileMaker va comprendre tout seul et exécuter la requête:

SELECT primaryKey FROM invoices WHERE clientName > 'L' AND invoiceDate = '2024-06-01' AND totalAmount > 333.33

(ajout des guillemets simples, transformation de la date, et utilisation du point comme séparateur décimal, même si mon fichier utilise la virgule, car SQL comprend le point).

Voilà pour ce rappel concernant ExecutesSQL, revenons maintenant à l’écriture de la fonction.

On aura donc un JSON écrit avec une fonction du type:[/vc_column_text][vc_column_text]

JSONSetElement ( ""
   ; [ "query" ; <un objet JSON compliqué auquel je réfléchirai dans le détail après> ; JSONObject ]
   ; [ "fieldSeparator" ; "" ; JSONString ]
   ; [ "rawSeparator" ; "" ; JSONString ]
   ; [ "arguments" ; <un array JSON puisque les arguments doivent être passés dans l'ordre> ; JSONArray ]
)

[/vc_column_text][/vc_column][/vc_row][vc_row bg_color=””][vc_column width=”1/1″][vc_column_text]Notons aussi que je souhaite écrire

• "{table}"."{column}" et nom simplement {column}

• "{table}" et non simplement {table}

Ceci afin d’éviter les problèmes de noms réservés de SQL ainsi que les nommages bizarre des champs avec par exemple une espace. Il est très important selon moi d’être dans cet état d’esprit : tout le monde doit pouvoir utiliser cette fonction, dans n’importe quel fichier FileMaker. Je ne prends donc ni pour acquis que les rubriques seront nommées “correctement” ou que le séparateur décimal d’un fichier sera forcément le mien, ou la langue le français (même si j’utilise la version anglaise de FileMaker). Donc toujours penser “ubiquité” : la fonction doit marcher partout, et si elle ne le peut pas, cela doit être conscient, assumé et documenté.[/vc_column_text][/vc_column][/vc_row][vc_row bg_color=””][vc_column width=”1/1″][vc_column_text]La structure “algorithmique” de la requête est donc :

"SELECT" & [ boucle sur une liste ordonnée de noms de colonnes ] & " FROM " & [ table, qui est une information que je peux extraire de la même liste si je qualifie pleinement les références aux rubriques avec la notation table::rubrique ]

Suivi d’une partie facultative (on n’est pas obligé d’avoir des critères) :

& " WHERE " & [ boucle sur un tableau de critères composés d'un opérateur logique (sauf pour le premier), d'une colonne, d'un opérateur de comparaison, et d'une valeur ]

[/vc_column_text][vc_column_text]Je me lance donc enfin dans l’écriture d’un prototype de JSON pour le paramètre query.

J’ouvre donc le DataViewer et…[/vc_column_text][vc_column_text]

JSONSetElement ( ""
   ; [ "query.columns[+]" ; GetFieldName ( invoice::invoiceNumber ) ; JSONString ]
   ; [ "query.columns[+]" ; GetFieldName ( invoice::invoiceDate ) ; JSONString ]
   ; [ "query.columns[+]" ; GetFieldName ( invoice::amount ) ; JSONString ]
)

[/vc_column_text][vc_column_text]Voici donc un premier tableau, celui du nom des colonnes (il s’agit bien du nom et non de la valeur, c’est pourquoi le type est toujours String).

Sauf que… je réalise que j’ai oublié les fonctions. Je sais que je ne veux pas les gérer tout de suite, mais il faut que ma structure permette l’évolution. J’ai aussi en tête que si un jour je veux gérer les jointures, il va me falloir un peu plus d’information sur les colonnes, mais ça, c’est pour plus tard. Je reprends :[/vc_column_text][vc_column_text]

JSONSetElement ( ""
   ; [ "query.columns[+].name" ; GetFieldName ( invoice::invoiceNumber ) ; JSONString ]
   ; [ "query.columns[:].function" ; "Sum" ; JSONString ]
   ; [ "query.columns[+].name" ; GetFieldName ( invoice::invoiceDate ) ; JSONString ]
   ; [ "query.columns[+].name" ; GetFieldName ( invoice::amount ) ; JSONString ]
)

[/vc_column_text][vc_column_text]Bien, maintenant j’ai un tableau qui représente la liste des colonnes, je dois maintenant en faire quelque chose.

Avant tout, il me faut un environnement pour ça. J’écris donc dans mon Visualiseur de données :[/vc_column_text][vc_column_text]

Let ([
   _json = JSONSetElement ( ""
         ; [ "query.columns[+].name" ; GetFieldName ( invoice::invoiceNumber ) ; JSONString ]
         ; [ "query.columns[:].function" ; "Sum" ; JSONString ]
         ; [ "query.columns[+].name" ; GetFieldName ( invoice::invoiceDate ) ; JSONString ]
         ; [ "query.columns[+].name" ; GetFieldName ( invoice::amount ) ; JSONString ]
      );
   _query = ""
];
   _query
)

[/vc_column_text][vc_column_text]Je peux maintenant attaquer le paramètre query, et observer le résultat.

Je ne vous remets pas tout le contenu du Visualiseur de données, juste le paramètre _query de la fonction Let (Definir en français)[/vc_column_text][vc_column_text]

"SELECT " & While ([
        j = _json ;
	          c = ValueCount ( JSONListKeys ( j ; "columns" )) ;
	          i = 0 ;
	          r = "" ;
	          v = "" ;
	          to = "" ;
	        toq = "" 
	     ];
	        i < c ;
	     [
		        v = Substitute ( JSONGetElement ( j ; "columns[" & i & "].name" ) ; "::" ; ¶ ) ;
		        function = JSONGetElement ( j ; "columns[" & i & "].function" ) ;
		        // as we are selecting from 1 table only, let's extract the table occurrence name only during the first iteration
		        to = Case ( i = 0 ; GetValue ( v ; 1 ) ; to ) ;
		        // quoted version, to avoid issue with reserved words or blanks
		        toq = Case ( i = 0 ; Quote ( to ) ; toq ) ;
		        column = GetValue ( v ; 2 ) ;
		        colq = Quote ( column ) ; // quoted version
		        // append to the list
		        r = List ( r ; Case ( IsEmpty ( function ) ; colq ; function & "(" & colq & ")" )) ;
		        i = i+1
	     ];
		        Substitute ( r ; ¶ ; ", " ) & " FROM " & to 
   )
)

[/vc_column_text][vc_column_text]D’abord, rassurez-vous, je n’ai pas écrit ça d’un seul coup, mais je ne peux pas décortiquer chaque étape, sans quoi vous allez vous ennuyer et la vaisselle dans l’évier va commencer à sentir…

Notez tout de même que :

• je ne me suis pas occupé des fonctions tout de suite
• j’utilise pour mes fonctions While (TantQue) un certain nombre de conventions qui me font aller assez vite :
  • l’itérateur est toujours i (sauf dans le cas d’une boucle imbriquée (While dans While, auquel cas c’est j)
  • je commence par c (le nombre d’itérations), et par initialiser i (l’itérateur), r (le résultat), et en général v (la valeur traitée)
  • la condition est presque toujours i < c
  • l’incrémentation de l’itérateur (i = i+1) intervient en premier paramètre de la partie logique, ou en dernier quand on travaille sur un array JSON (0-based)
• je n’ai ajouté les commentaires que plus tard

Vous pourriez aussi vous demander pourquoi avoir to et toq, column et colq, et pas seulement la version q (entre guillemets). Vous avez raison, à ce stade ça n’est pas nécessaire. Je les ai laissés ici pour que la comparaison avec les versions suivantes soient facilitées (je vous mâche le travail !)[/vc_column_text][vc_column_text]

Clause WHERE

Je peux donc passer aux critères.

Je commence à écrire le tableau (array), sauf que là, il me faut quelques données, ou du moins, à ce stade, une table et des rubriques.

Je demande à ChatGPT de me fournir un échantillon de données en csv, en faisant bien attention d’avoir différents types (texte (dont au moins une colonne contenant des textes multi-lignes), nombre, date) et des doublons (pour pouvoir faire des regroupements). Je m’assure aussi d’avoir des colonnes dont les noms comportent des espaces ou des caractères accentués. Je glisse mon CSV sur l’icone de FileMaker qui le convertit en fichier .fmp12 et hop, on peut travailler “pour de vrai”. En fait, pas tout à fait encore. Je dois d’abord convertir les données de type date qui sont au format YYYY-MM-DD au format FileMaker, qui dans mon fichier est DD/MM/YYYY. Cela aurait dû me mettre la puce à l’oreille, mais… vous allez voir.

Retour au Visualiseur de données.[/vc_column_text][vc_column_text]

JSONSetElement ( "" 
   ; [ "criteria[+].column" ; GetFieldName ( invocies::total ) ; JSONString ]
   ; [ "criteria[:].operator" ; "<" ; JSONString ]
   ; [ "criteria[:].value" ; 100.23 ; JSONNumber ]
   ; [ "criteria[+].column" ; GetFieldName ( invoices::invoiceNumber ) ; JSONString ]
   ; [ "criteria[:].operator" ; "LIKE" ; JSONString ]
   ; [ "criteria[:].logicalOperator" ; "OR" ; JSONString ]
   ; [ "criteria[:].value" ; "NC%" ; JSONString ]
   ; [ "criteria[+].column" ; GetFieldName ( invoices::clientVatNumber ) ; JSONString ]
   ; [ "criteria[:].operator" ; "=" ; JSONString ]
   ; [ "criteria[:].value" ; "*" ; JSONString ]
   ; [ "criteria[+].column" ; GetFieldName ( invoices::taxTotal ) ; JSONString ]
   ; [ "criteria[:].operator" ; "=" ; JSONString ]
   ; [ "criteria[:].value" ; "=" ; JSONString ]
   ; [ "criteria[+].column" ; GetFieldName ( invoices::date ) ; JSONString ]
   ; [ "criteria[:].operator" ; ">" ; JSONString ]
   ; [ "criteria[:].value" ; Date ( 12 ; 1 ; 2015 ) ; JSONString ]
)

[/vc_column_text][vc_column_text]Comme vous le voyez, avec cette nouvelle notation [+] et [:] de la version 21, il est vraiment très aisé d’écrire un array de manière naturelle. Avec [+], je commence une nouvelle rangée, avec [:] j’ajoute une “colonne” (“attribut”) en restant dans la même rangée.

Je peux donc écrire presque naturellement, et donc au cours de l’écriture me viennent des idées. Par exemple l’idée d’utiliser des opérateurs de recherche classiques et bien pratiques en FileMaker tel que “=” (vide) et “*” (non vide). Je prévois donc ces cas dans le prototype en pensant que je les traiterais pour des opérateurs IS NULL et IS NOT NULL en SQL. L’important ici est que je n’y avais pas pensé avant de commencer, et que je juge au fil de l’eau que ce sera assez facile à gérer et que donc je vais l’intégrer dès la première version.

Premier écueil

En revanche, en écrivant ce prototype je me rends compte d’une chose à laquelle je n’avais pas pensé (j’aurais dû, mais je dois bien me rendre à l’évidence, au risque de décevoir ma mère : je suis imparfait)
Note: pour ma grand mère, dont je vous parlais ici, c’est tout noir ou tout blanc, elle est très booléenne.

Et donc, ce problème, c’est qu’en arrivant à la comparaison de date, je me rends compte que si je passe comme je le voulais les arguments sous forme de ? pour qu’ils soient substitués pas les valeurs (voir plus haut sur la syntaxe de ExecuteSQL), le type sera forcément perdu par un passage par JSON, JSON étant dépourvu de type date, et donc FileMaker sera incapable de passer le bon type de donnée (il n’y verra que du texte ou du nombre).

Cela s’ajoute à ce que j’avais déjà en tête, à savoir que pour passer un nombre variable de paramètres à une fonction (ce qu’indiquent les {} (facultatif) et les … (nombre variable) dans l’empreinte de la fonction

ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )

Et ça, je sais que ça va m’obliger à utiliser l’indirection et la fonction Evaluate… et je n’ai pas très envie à ce stade, en particulier quand on est dans un contexte où on joue avec l’indépendance des noms des champs, avec des guillemets, des conversions de type… Bref, le truc pénible qui va compliquer l’écriture de cette fonction, sa maintenance, et éventuellement sa qualité. On verra plus tard qu’on y arrivera, à l’indirection (Evaluate), mais pour d’autres raisons.

Donc je laisse tomber l’idée de passer les arguments avec des ?, et je dois donc insérer directement les valeurs correctement dans le JSON à l’aide d’une fonction que j’utilisais déjà pour écrire les requêtes SQL : sql.getAsSqlData ( _data ; _type ). C’est vrai pour les données de types date, heure et horodatage. Les nombres sont bien gérés par JSON.

D’ailleurs, en écrivant cet article, je me rends compte que le nom même de la fonction json2sql n’est pas forcément le bon. Dans notre bibliothèque de fonction, le premier mot nous permet de regrouper alphabétiquement les fonctions selon ce qu’elles traitent. Nous avons une ribambelle de text.<quelque chose>, de json.<quelque chose>… or ici, il me semble que le sujet est plus le SQL que le JSON. Donc je devrais probablement renommer la fonction sql.fromJson… je vais devoir encore réfléchir un peu à ça.[/vc_column_text][vc_column_text]Cette mésaventure me pousse à reconsidérer l’empreinte que j’avais choisie, avec un simple paramètre en JSON.

Sans que ce soit évident (je pourrais très bien continuer dans cette voie, je me dis que le fait de passer les paramètres fieldSeparator et rowSeparator dans le JSON ne mettra pas en valeur la différence avec ExecuteSQL et la manière de passer les arguments. Je décide de revenir sur cette décision et de passer à l’empreinte suivante :

json2sql ( _jsonQuery ; _fieldSeparator ; _rowSeparator )

qui ressemble plus à l’empreinte de la fonction native :

ExecuteSQL ( sqlQuery ; fieldSeparator ; rowSeparator { ; arguments... } )

et cela allège un peu la documentation que l’utilisateur (développeur) devra lire pour utiliser la fonction.

Cela aussi, ça fait partie de “l’état d’esprit” dans lequel je me mets pour écrire une fonction personnalisée : j’essaie de respecter les usages de la plateforme FileMaker, et d’être aussi cohérent que possible. Je ne renonce pas pour autant aux _ (underscores) devant les paramètres : d’abord ils rappellent qu’il s’agit d’une fonction personnalisée, et ensuite c’est une convention que je trouve assez importante dans mon code, mais on ne va pas discuter conventions de nommage…[/vc_column_text][vc_column_text]J’ajoute donc à ma fonction la partie qui s’occupe des critères (clause WHERE) :[/vc_column_text][vc_column_text]

<the first part of the function here>
& Case ( not IsEmpty ( JSONListKeys ( j ; "criteria" )) ; 
   " WHERE " &
   While ([
      i = 0 ;
      c = ValueCount ( JSONListKeys ( j ; "criteria" )) ;
      v = "" ;
      r = ""
   ];
      i < c ;
   [
      f = GetValue ( Substitute ( JSONGetElement ( j ; "criteria[" & i & "].column" ) ; "::" ; ¶ ) ; 2 ) ;
      o = JSONGetElement ( j ; "criteria[" & i & "].operator" ) ;
      o = Case ( IsEmpty ( o ) ; "=" ; o ) ; // default comparison operator is =
      lo = JSONGetElement ( j ; "criteria[" & i & "].logicalOperator" ) ; 
      lo = Case ( IsEmpty ( lo ) ; "AND" ; lo ) ; // default logical operator is AND
      value = JSONGetElement ( j ; "criteria[" & i & "].value" ) ; 
      type = JSONGetElementType (  j ; "criteria[" & i & "].value" ) ;
      // this ugly things converts the data to JSON then parses it as a string to handle decimal separators and single quotes.
      v = Case ( type = JSONNumber or type = JSONBoolean ; Substitute ( JSONSetElement ( "" ; "v" ; value ; JSONNumber ) ; [ "{\"v\":" ; "" ] ; [ "}" ; "" ] ) ; "'" & value & "'" ) ;
      r = r & Case ( i ; " " & lo & " " ) // the logical operator is omitted for the first criterion
          & Quote ( f ) & " " & Case ( o = "=" and ( IsEmpty ( value ) or value = "=" ) ; "IS NULL" ; o = "=" and value = "*" ; "IS NOT NULL" ;  o & " " &  v ) ;
      i = i+1 
   ] ;
      r
   )
)

[/vc_column_text][vc_column_text]Quelques commentaires :

• cette partie est conditionnée au fait qu’on trouve bien un tableau (array) “criteria” dans le JSON, j’aurais pu inclure le ” WHERE ” dans le résultat du While, mais je trouve que c’est plus lisible ainsi. On voit tout de suite qu’il s’agit de la clause WHERE de la requête.
• pour l’opérateur de comparaison (o), on voit que s’il est vide, j’utilise “=”, valeur par défaut. Penser à des valeurs par défaut permet de faciliter l’usage de la fonction.
• pour l’opérateur logique, c’est AND par défaut
• comme prévu, le traitement de * et de = est très simple (voir la définition de r)

[/vc_column_text][vc_column_text]Je passe rapidement sur les autres clauses GROUP BY, ORDER BY, OFFSET et FETCH FIRST. Elles sont assez facilement compréhensibles dans le code de la fonction.

Notons simplement que j’ai volontairement exclus le OFFSET par pourcentage, parce que ça introduisait une ambiguité au niveau du paramètre JSON : dois-je envoyer un nombre (5) ou un texte (“5%”). Et puis je vous avoue que je n’ai jamais utilisé ce pourcentage, donc pas envie d’ajouter de la complexité pour rien, en tout cas pas dans la première version.[/vc_column_text][vc_column_text]

// GROUP BY
& Case ( not IsEmpty ( JSONListKeys ( j ; "group" )) ; " GROUP BY " &

   While ([
      i = 0 ;
      c = ValueCount ( JSONListKeys ( j ; "group" )) ;
      f = "" ;
      r = ""
   ];
      i < c ;
   [
      f = GetValue ( Substitute ( JSONGetElement ( j ; "group[" & i & "].column" ) ; "::" ; ¶ ) ; 2 ) ;
      r = List ( r ; Quote ( f )) ;
      i = i+1 
   ] ;
      Substitute ( r ; ¶ ; ", " )
   )
)

// ORDER BY
& Case ( not IsEmpty ( JSONListKeys ( j ; "sort" )) ; " ORDER BY " &
   While ([
      i = 0 ;
      c = ValueCount ( JSONListKeys ( j ; "sort" )) ;
      d = "" ;
      r = ""
   ];
      i < c ;
   [
      f = GetValue ( Substitute ( JSONGetElement ( j ; "sort[" & i & "].column" ) ; "::" ; ¶ ) ; 2 ) ;
      d = JSONGetElement ( j ; "sort[" & i & "].dir" ) ; 
      d = Case ( IsEmpty ( d ) ; "A" ; d ) ;
      r = List ( r ; Quote ( f ) & Case ( Left ( d ; 1 ) = "d" ; " DESC" ; " ASC" )) ;
      i = i+1 
   ] ;
      Substitute ( r ; ¶ ; ", " )
   )
)

// OFFSET and FETCH FIRST
& Case ( JSONGetElement ( j ; "offset" ) ; " OFFSET " & JSONGetElement ( j ; "offset" ) & " ROWS" )
& Case ( JSONGetElement ( j ; "limit" ) ; " FETCH FIRST " & JSONGetElement ( j ; "limit" ) & " ROWS ONLY" )

[/vc_column_text][vc_column_text]

Exécution du SQL et c’est fini !

Et donc me voilà avec une fonction qui crée un parfaite requête SQL à partir d’un parmètre JSON. Je n’avais plus qu’à exécuter le SQL et retourner le résultat.

Rien de plus simple.

Quelques tests. Ça marche du feu de Dieu ! (pour les plaintes en blasphèmes, adressez-vous au guichet d’à côté). Les requêtes fonctionnent, j’obtiens bien les bons résultats, avec ou sans fonctions, avec ou sans critères…

Et puis… j’ai eu une idée.

Et c’est donc là que les ennuis ont commencé…

… mais je vous les raconterai dans une seconde partie.[/vc_column_text][vc_column_text]

Conclusions de la première partie

• se mettre dans un certain état d’esprit. Ici je sais que je travaille sur une fonction générique, elle doit donc être capable de fonctionner dans n’importe quel contexte, et être facilement utilisable pas n’importe quel développeur. Quand je crée une fonction dans le cadre d’un projet client, je préfixe le nom de la fonction du nom du projet, et en fin de projet, je parcours ces fonctions pour voir si certaines mériteraient d’être rendues génériques et d’entrer dans notre boîte à outil. Mais si certaines se qualifient, ce n’est JAMAIS sans une revue du code pour qu’il soit présenté de manière cohérente avec nos autres fonctions, qu’il prévoie bien des cas dont nous n’avions pas besoin dans ce projet spécifique…
• dans la mesure du possible, respecter la plateforme FileMaker et écrire les fonctions d’une manière qui les rendra utilisables assez naturellement dans ce cadre.
• penser à gérer des valeurs par défaut pour ne pas contraindre l’utilisateur (le développeur) à renseigner tous les paramètres.
• planifier, mais pas trop. J’ai une idée de ce que dois faire la fonction, je diffère d’emblée certaines possibilités (ici les jointures par exemple), mais d’un autre côté je ne m’interdis pas d’avoir des idées en cours de route (le traitement des opérateurs de comparaison = et * dans cet exemple). Comme vous le verrez dans la seconde partie, avoir eu une idée en cours de route m’a coûté très cher en temps, mais d’un autre côté le résultat méritait cette peine.
• travailler avec des données. Partir du concret et aller vers l’abstrait. Beaucoup plus efficace que de monter toute une construction mentale et d’ensuite la confronter à la réalité et se rendre compte que rien ne va.
• penser aux types de données, et garder en tête que quand on va boucler (While), les textes multi-lignes sont en soi un type de données.
• avoir des conventions (ici la structure de la boucle While, entre autres) et s’y tenir.

[/vc_column_text][/vc_column][/vc_row]

[vc_row bg_color=””][vc_column width=”1/1″][vc_column_text]Comme nous l’avons vu dans cette revue détaillée des nouveautés de FileMaker 2023, une des deux fonctionnalités phares, avec l’exécution de script sur serveur avec call-back, est ce que Claris appelle “Audit Log”

Il y a tellement de choses à savoir sur cette fonctionnalité que nous avons préféré lui consacrer un billet complet.[/vc_column_text][ish_headline tag_size=”h2″]Pas un audit log[/ish_headline][vc_column_text]Tout d’abord mettons-nous d’accord, même si le marketing de Claris n’a pas résisté à la tentation de l’appeler ainsi, cette fonctionnalité n’est pas un réel audit log, et ce pour plusieurs raison.

• D’abord pour une raison assez simple : cette fonctionnalité ne logue rien du tout. Elle permet éventuellement à un développeur de bâtir son audit log dessus.
• Ensuite un audit log, pour être digne de se nom, doit répondre à certains impératif d’inviolabilité (on ne doit pas pouvoir faire une modification sans qu’elle soit loguée, on ne doit pas pouvoir modifier une entrée dans le log…). Même si on verra que c’est ce que FileMaker pouvait proposer de mieux, la nouvelle fonctionnalité ne répond pas à ces critères.

Est-ce à dire qu’il faut passer son chemin sans regarder ? Pas du tout ! La nouvelle fonctionnalité est enthousiasmante, mais comme disait Camus, “mal nommer les choses c’est s’exposer à la critique”  (ou un truc comme ça). Et s’exposer à la critique d’un auditeur externe à qui on aurait vendu un “audit log” qui n’en serait pas un… voilà bien une situation que je préférerais éviter.

Donc non, l’audit log n’est pas un audit log. Soit. Comment l’appeler alors ?[/vc_column_text][ish_headline tag_size=”h2″]Transactions de fenêtre[/ish_headline][vc_column_text]C’est le nom “technique” de la fonctionnalité. C’est sous ce nom qu’on la retrouve dans FileMaker Pro, et ça me semble être déjà beaucoup plus proche de la réalité.

Enfin, c’est ainsi qu’on la retrouve dans FileMaker Pro… en anglais (OnWindowTransaction), car le déclencheur a été étrangement traduit par SurOperationFenetre en français, mais à moins que l’on parle de finance ou de compatibilité, c’est bien de transaction qu’il s’agit ici. Je réalise en écrivant que les transactions de la version 19.6 sont également, partiellement traduites en “opérations”, mais pas le nom de la fonction Obtenir ( EtatTransactionOuvert ).

Les transactions de fenêtres apparaissent sous la forme d’un déclencheur de script dans les options de fichier, et vont s’appliquer à toutes les transactions.

Une fois activé, chaque transaction (ou presque) déclenchera le script.

Qu’est-ce qu’une transaction ? La question se pose car quand on nomme mal les choses… et depuis 30 ans, il faut dire que nommer les choses bizarrement est une spécialité de FileMaker (essayez de parler de rubriques à des pros de la base de données, ou de modèles à des designers, et vous verrez ce que je veux dire). Donc une transaction n’est pas particulièrement ce que Claris a appelé transaction dans la version 19.6.
Une transaction, c’est simplement le fait de créer, modifier, supprimer un ensemble d’enregistrements (y compris un seul enregistrement) et de valider. Ça existait déjà avant la 19.6, même si la 19.6 a permis d’écrire des transactions plus facilement, ou de rendre certaines actions “transactionnelles” alors que ça n’était pas possible préalablement.
Notons que la modification d’une rubrique globale, qui n’est pas a proprement parler une modification de données, ne provoque pas de transaction de fenêtre, (sauf bien sûr si la modification d’une rubrique globale entraîne une modification d’une autre rubrique, standard elle, par le truchement d’une auto-entrée en résultat de calcul).

Bref, vous avez compris ce qu’est une transaction de données. Lors d’une transaction on peut :

• créer des enregistrements
• modifier des enregistrements
• supprimer des enregistrements

Les transactions de fenêtre sont en FileMaker ce qui se rapproche le plus d’une transaction de données, ou plus exactement d’un événement qui a lieu sur transaction de données.

Mais ce sont des transactions… de fenêtre. Encore faut il donc avoir une fenêtre, même virtuelle.

Autrement dit, sont exclus de ces transactions les interactions directes avec la couche data :

• Data API (sauf si elle exécute un script)
• OData (sauf si elle exécute un script)
• PHP/XML (sauf s’ils exécutent un script)
• ODBC
• Instruction de script Tronquer la table
• Un fichier est défini comme une source de données externes d’un fichier d’interface dans lequel les transactions de fenêtres sont activées (déclencheur actif), mais les données sont modifiées depuis un autre fichier.

Voilà pourquoi même si un système de log était fourni, cette fonctionnalité ne permettrait pas d’avoir un réel “audit log”.[/vc_column_text][ish_headline tag_size=”h2″]Description des transactions de fenêtre[/ish_headline][vc_column_text]Nous l’avons déjà évoqué, l’interface de configuration se trouve dans les options de fichier.

On peut sélectionner le script qui sera activé quand on valide une transaction, et indiquer facultativement le nom hard-codé d’une rubrique. Comme pour les autres déclencheurs au niveau du fichier, seul un script du fichier en cours peut être sélectionné. Dans ce cas ci, c’est un peu dommage, mais on comprend bien la raison.

Quand une transaction est validée, le script sera déclenché et recevra automatiquement un paramètre en JSON tel que :[/vc_column_text][/vc_column][/vc_row][vc_row bg_color=””][vc_column width=”1/1″][vc_column_text]

{
   "nomDuFichier":
   {
       "nomDeLaTable":
       [
             [ //(pour chaque enregistrement):
                  type de modification ("New", "Modified" ; "Deleted"),
                  ID de l'enregistrement,
                  "paramètre facultatif"
             ];
             [ 
                  type de modification ("New", "Modified" ; "Deleted"),
                  ID de l'enregistrement,
                  "paramètre facultatif"
             ]
        ]
   }
}

[/vc_column_text][vc_column_text]Comme vous l’avez repéré du premier coup d’œil car vous êtes désormais des experts en JSON, les enregistrements modifiés sont les éléments d’un tableau (array), avec 3 informations dont il faut donc connaître l’ordre : le type de modification (New, Modified, Deleted), l’ID de l’enregistrement, et le paramètre facultatif, qui sera présent mais vide si vous ne l’avez pas défini.

L’autre chose que vous avez immédiatement repérée est qu’il n’y a pas de trace de données ou de noms de rubriques modifiées.[/vc_column_text][ish_headline tag_size=”h3″]Le paramètre facultatif[/ish_headline][vc_column_text]Le contenu du paramètre facultatif provient de la fameuse rubrique que vous avez pu choisir dans les options du fichier.

Si vous n’avez pas indiqué de nom de rubrique et qu’il existe une rubrique du nom de OnWindowTransaction (un truc que vous n’auriez pas inventé si on ne vous l’avait pas dit… un peu comme la chanson de la Sorcière du placard aux balais), cette rubrique sera prise en compte.

Donc au moment de la validation de la transaction, pour chaque enregistrement créé, modifié, ou supprimé (dans ce dernier cas la rubrique est évaluée avant la suppression), la rubrique -qui peut bien sûr être calculée- est évaluée et son contenu transmis comme paramètre facultatif pour cet enregistrement.

JSON

Notons que si le contenu de la rubrique est un JSON, ce JSON n’est pas rendu en string (“stringifié”). Pour être très précis, afin de ne pas perdre trop de temps à valider chaque JSON, ce qui ralentirait fortement les transactions avec beaucoup d’enregistrements, FileMaker considère que si le contenu commence et finit par { et } ou [ et ], il s’agit d’un JSON valide.[/vc_column_text][ish_headline tag_size=”h3″]Transactions massives[/ish_headline][vc_column_text]Il existe neuf manières de modifier plusieurs enregistrements en une opération avec FileMaker.

Certaines d’entre elles sont parfaitement gérées par les transactions de fenêtres, d’autres non.

Eliminons d’abord celles qui ne sont pas gérées, mais gardons-les en tête si nous voulons vraiment parler d’audit log :

• requêtes externes directement sur la couche données (Data API, OData, PHP/XML, ODBC). Dans le cas d’exécution de script (et donc de l’existence d’une fenêtre), les transactions de fenêtre auront lieu.
• action de script Tronquer la table.
• modification du schéma de données (on peut supprimer une table, créer une rubrique calculée ou modifier une formule de calcul, valider la modification, puis changer le type de rubrique pour en faire une rubrique standard, et les données ont été modifiées sans qu’il y ait de transaction de fenêtre.
• ajoutons une variante de cette dernière : la création de fichier par conversion. Si par exemple vous glissez un fichier Excel sur l’icône de FileMaker, vous obtiendrez un fichier avec déjà une table et des données, avant même d’avoir pu configurer le moindre déclencheur ou le moindre script.

Les méthodes gérées sont donc :

Les méthodes non transactionnelles :

• Les importations (Importer enregistrements, Importer un dossier, Importer depuis une source ODBC) sont capables de créer ou modifier des données, ou les deux à la fois.
• Supprimer tous les enregistrements (ou Supprimer les enregistrements trouvés, selon qu’on a fait une sélection préalablement ou non)
• Remplacer le contenu d’une rubrique

Les méthodes transactionnelles :

• Les transactions par lien, telles qu’elles existent depuis que FileMaker est relationnel (FileMaker 3, sorti en 1996) : on peut modifier l’enregistrement principal (celui affiché) ainsi que créer, modifier, supprimer des enregistrements liés -la suppression nécessitant une table externe.
• Les transactions de FileMaker 19.6, qui sont la même chose si ce n’est que :
  • elles peuvent être faites en changeant de contexte (on n’a donc plus besoin de lien)
  • elles peuvent inclure les autres types de transactions massive (importation, suppression de l’ensemble trouvé, remplacer)

Importance de l’utilisation des transactions de script “de la 19.6” pour les transactions massives

Pour les méthodes transactionnelles, c’est assez évident : une transaction occasionnera une et une seule transaction de fenêtre (tous les enregistrements créés, modifiés et supprimés se retrouveront  donc dans le paramètre du script)

Quant aux trois autres méthodes (import, supprimer tous, remplacer), il est très avantageux de les inclure également dans une transaction.

En effet, contrairement à notre FM AuditLog Pro (qui sera bien évidemment mis à jour très prochainement pour tirer parti de cette nouvelle fonctionnalité) qui parvenait à réunir les différentes transactions réelles en une seule transaction logique, les transactions de fenêtres reflètent exactement le fonctionnement interne de FileMaker.

Par exemple, quand on supprime tous les enregistrements, FileMaker les supprime 100 par 100. Quand on importe, cela dépend du format du fichier (par tranches de 25 pour le format csv, mais par tranches alternées de 25 et de 1000 pour le format mer (un csv avec les titres de colonnes), par tranches de 500 pour Remplacer contenu rubrique…
Bref, c’est tout ce que j’avais découvert en développant FM AuditLog Pro, mais cette “popote interne” n’intéresse sûrement pas l’utilisateur et on aurait préféré qu’un import ou une suppression soit résumé en une transaction. Or ça n’est pas le cas, le script SurOperationFenetre sera déclenché autant de fois qu’il y a de transactions internes. Il est donc très important d’encapsuler ces opérations dans une transaction de script.

Conseil : si l’utilisateur peut lui-même procéder à un import, à une suppression de l’ensemble trouvé, ou à un remplacement de rubrique, nous recommandons d’utiliser les menus personnalisés pour remplacer ces commandes, avec des scripts associés tels que :

Ouvrir une opération
   Importer enregistrements
Valider l'opération

Reste le cas marginal où l’utilisateur est déjà dans le cas d’une transaction (opération) avec un script en pause. Si on veut éviter l’erreur 3 pour des transactions imbriquées (erreur qui ne pose pas de problème mais c’est moins joli et on aime bien que ce soit joli), alors on peut écrire :

Si [ Obtenir ( EtatOperationOuvert )]
   Importer enregistrements
Sinon
   Ouvrir une opération
      Importer enregistrements
   Valider l'opération
Fin de si

Data API, OData, XML

Pour ce qui est de ces modes d’interaction avec FileMaker, il existe la possibilité de privilégier les scripts. Afin de garantir la validité d’un audit log (au sens strict), on pourra, dans les réglages de sécurité, s’assurer que ces modes d’interaction ne peuvent modifier des enregistrements que si un script est en cours d’exécution.

not EstVide ( Obtenir ( NomScript ))

Mais cela se fait malheureusement au prix d’une baisse des performances.

On peut vraiment regretter que le pas de script Exécuter FileMaker Data API soit limité à la lecture. Il serait tellement plus simple de convertir les appels à l’API en scripts…[/vc_column_text][ish_headline tag_size=”h2″]Drag and drop (glisser/déposer) et Remplacer[/ish_headline][vc_column_text]Depuis toujours, deux événements se distinguent dans la manière de modifier des données : le drag and drop et Remplacer contenu de rubrique.

En effet, ces deux événements ont la particularité de pouvoir opérer sur des enregistrements non ouverts préalablement, de modifier les enregistrements, et de les garder “fermés”, sans déclencher d’événement SurValidationEnregistrement donc.

Dans le cas du glisser/déposer, cela va même encore plus loin puisque l’on peut carrément modifier un enregistrement alors qu’il est ouvert par un autre utilisateur !

Et bien c’est une excellente nouvelle : les transactions de fenêtre permettent désormais de capter ces événements. Si l’enregistrement actif n’est pas ouvert quand on entame une action de remplacement ou que l’on glisse un contenu sur une rubrique, alors une transaction de fenêtre sera déclenchée après l’événement.

En terme d’intégrité des données, c’est un gros progrès ![/vc_column_text][ish_headline tag_size=”h2″]Difficultés[/ish_headline][vc_column_text]Une des difficultés que l’on rencontrera en mettant en place un script pour logguer ses transactions, c’est d’inhiber le script après avoir enregistré la transaction.

Souvenons-nous, toutes les transactions ayant lieu dans une fenêtre du fichier déclenchent le script.

Parade : écrire en début de script une condition de sortie comme

Si [ Obtenir ( NomModèle ) = <leModèleSurLequelJeLogueLesTransactions>
   Fin de Script
Fin de Si
Suite du script

Un bug ennuyeux

La version qui sort aujourd’hui comporte un bug très ennuyeux. Gageons qu’il sera vite réglé lors d’une prochaine mise à jour.

Lors de l’exécution du script SurOperationFenetre, l’action de script Fermer Fenêtre est tout simplement ignorée. Elle ne retourne pas d’erreur mais est sans effet.

C’est très embêtant car comme souvent, quand on ne veut pas perdre le contexte de l’utilisateur (onglets sélectionnés, position de défilement de la fenêtre et des tables externes…) un script classique serait de la forme :

Nouvelle fenêtre
Activer modèle [ <modèleOùJeLogueLesTransactions> ]
Nouvel enregistrement
Définir rubrique [ rubrique ; Obtenir ( ParamètreScript )]
Fermer Fenêtre

Or la dernière action ne sera pas prise en compte ! C’est embêtant.

En attendant la correction, deux parades à cela :

• dès le départ, transmettre le paramètre à un script exécuté sur serveur, mais cela présente deux inconvénients majeurs :
  • si l’on n’est pas sur serveur (c’est encore le cas dans de nombreuses situations, notamment pour des applications asynchrones sur iPhone ou iPad)
  • sur des grosses transactions, la taille limite du paramètre pourrait être atteinte (un million de caractères)
• utiliser un déclencheur SurTemporisation. Le script prend donc la forme :

Nouvelle fenêtre
Activer modèle [ <modèleOùJeLogueLesTransactions> ]
Nouvel enregistrement
Définir rubrique [ rubrique ; Obtenir ( ParamètreScript )]
Installer un script sur temporisation [ script: Fermer fenêtre; Intervalle : 0,00001 ]
Fin de script

Et bien sûr le script Fermer fenêtre… ferme la fenêtre ! (Bravo si vous aviez deviné ! 😉 )[/vc_column_text][ish_headline tag_size=”h2″]Un audit log… pour quoi faire ?[/ish_headline][vc_column_text]Si vous avez lu jusqu’ici, vous allez être récompensé(e), car au-delà des aspects techniques, la grande question est : “que faire de cette nouvelle fonctionnalité ?”

Or vous allez le voir, le potentiel est énorme -“juste énorme !”, comme disent ceux qui sont nés après la sortie de FileMaker 7.

Exemples d’utilisation :

• Bien sûr, garder la trace des modifications. Cela tombe sous le sens mais il ne faut pas l’oublier
• Mettre à jour des enregistrements liés, voir des “vues”. C’est un éternel challenge avec FileMaker -dépourvu de déclencheurs de tables (OnUpdate)- de mettre à jour des enregistrements liés. Par exemple si je mets à jour le montant d’un paiement je veux que la facture correspondante soit mise à jour, ainsi que la fiche client (pour connaître son solde). Dorénavant, je peux capter à coup sûr qu’un enregistrement de la table PAIEMENTS a été créé/modifié/supprimé et mettre à jour les enregistrements correspondants.
  On peut facilement envisager des tables de vues qui remplacent les vues liste et qui synthétisent l’information que l’utilisateur doit voir, sans calcul et sans lien, afin d’optimiser la vitesse de défilement ou de tri).
  Par exemple un enregistrement de la table CLIENTS, qui est liée à n factures, n commandes, n paiements… peut avoir son pendant dans la table CLIENTS_VUE, avec des rubriques standard (non calculées) qui permettent d’afficher des listes très rapides. Il est en effet relativement facile de développer une logique qui provoque le rafraichissement d’un tel enregistrement dès qu’un paiement, une commande ou une facture est modifié dans une transaction.
• “Réplication”. avec un outil comme FM AuditLog Pro 3.0 en préparation, il sera possible non seulement de faire du Roll-back comme avec FM AuditLog Pro 2.0, mais on pourra ré-exécuter les transactions sur un autre serveur, par exemple dans un scénario ou des utilisateurs travaillent sur plusieurs continents, chacun sur son serveur, mais où les modifications doivent être reportées sur un serveur central.

Voilà, j’espère que ces quelques pistes vous auront permis d’envisager le potentiel de cette nouvelle fonctionnalité et que vous avez apprécié cet article au point de le partager sur les réseaux.[/vc_column_text][/vc_column][/vc_row]