Fonctions SQL

Avec la sortie de FileMaker 12, la fonction ExecuterSQL permet au développeur FileMaker de formuler des requêtes complexes beaucoup plus facilement qu’avec le graphique des liens et des calculs.

Un autre -immense- avantage des requêtes SQL est qu’elle sont exécutées indépendamment du contexte. Il n’est donc plus nécessaire, dans un script, de multiplier les changements de modèles ou ouvertures de fenêtres, coûteux en temps de chargement et inesthétiques en FileMaker Go ou sous Windows.

Et puis, il y a cet immense avantage par rapport aux plugins : la fonction est compatible avec FileMaker Go !

Tout est pour le mieux donc, mais il existe des limites à cette « magie ».

• la fonction ExecuterSQL est limitée à la requête SELECT, c’est-à-dire que l’on peut lire, mais pas écrire dans la base de données. Cette limite est d’autant moins compréhensible que les plugins ou des clients ODBC sont autorisés à effectuer ces opérations via l’interface sql.
• dans la même veine, on peut regretter l’absence de paramètre nous permettant de préciser à quel fichier FileMaker on s’adresse. Les plugins, ainsi d’ailleurs que les fonctions de conception de FileMaker, peuvent interroger un autre fichier que le fichier actif (à condition qu’il soit ouvert), sans même que le fichier soit référencé par le fichier actif. Ceci est très pratique car cela permet d’éviter des dépendances inutiles. Intéressant par exemple quand on a un fichier embarqué sur un iPhone qui doit régulièrement synchroniser ses informations avec un fichier sur serveur, sans pour autant que l’on souhaite une connexion permanente. Cette absence est d’autant pus surprenante que le nouveau support du protocole URL nous donne une nouvelle arme pour cette communication entre fichiers.
• l’interface SQL travaille sur son propre système d’indexation, ce qui veut dire que la première requête sur une rubrique d’une table comportant beaucoup d’enregistrements va potentiellement prendre plusieurs secondes là où un lien FileMaker aurait donné un résultat immédiat. Fort heureusement, une fois cette première requête effectuée, les opérations seront très rapides. Etrangement, on voit là encore une différence dans les performances, au très net avantage des plugins, notamment du meilleur d’entre eux, DoSQL de myFMbutler.
• le pendant de l’indépendance du contexte, c’est que… ExecuterSQL ne peut pas du tout tirer parti du contexte. On ne peut pas influencer le résultat d’une requête avec le graphique des liens. Mais est-ce vraiment une limite ?
• cela nous mène à la quatrième limite, la plus importante sans doute, parce qu’elle se situe « entre la chaise et le clavier » : la plupart des utilisateurs ou développeurs FileMaker n’ont pas l’habitude de formuler des requêtes SQL. La logique du graphique des liens et des calculs nous a « formatés » et il ne nous est pas forcément évident de tirer profit.

C’est sur cette dernière limite que nous nous penchons aujourd’hui, parce qu’elle semble être la seule sur laquelle nous puissions influencer à court terme.

Car fort heureusement, nous sommes humains (enfin, je ne peux le garantir pour vous, mais en ce qui me concerne, c’est une certitude). Or homo sapiens est très doué pour l’apprentissage, et en particulier avec un sujet aussi « simple » que SQL. Sérieusement, les rudiments de SQL (ceux que l’on utilise pour 95% des requêtes) s’apprennent en quelques minutes. Personnellement, quand je sèche, je regarde ici.

Il existe aussi de nombreux forums dédiés à SQL, où certains ont déjà publié des requêtes compliquées. Ainsi, si je n’ai aucune idée de la manière de sélectionner les factures de 2009 à 2012, regroupées par mois et classée par montant décroissant… je pose ma question littéralement sur Google. Si j’ai la chance de parler l’anglais, je la pose en Anglais pour plus de réponses. Et il est rare que je ne trouve pas une réponse toute faite ou approchant sensiblement.

Mais puisqu’il faut s’y mettre, autant essayer un peu par soi-même. Et vous le verrez, on arrive très vite à quelque chose.

Seulement, au bout de quelques requêtes plus ou moins péniblement formulées, vous vous rendrez compte que :

• le nom des rubriques et des tables est « hard-codé » dans la requête. SQL nous ferait donc perdre l’avantage immense de FileMaker de ne pas avoir à se soucier du nommage ?
• vous utilisez exactement la même requête plusieurs fois, pour des rubriques et des tables différentes. Typiquement, c’est celle-ci : « donne moi la liste du (des) enregistrement(s) dans la table X dans lequel (lesquels) la rubrique Y a le contenu Z. Plus concrètement : « donne-moi tous les ID de mes pots de confiture dont le fruit est Abricot. » Ou au contraire, « donne-moi le fruit du pot de confiture dont l’ID est 348 »

Pour pallier à ces deux « problèmes », qui encore une fois ne viennent pas de la technique mais de nous-mêmes, j’ai publié une fonction personnalisée qui devrait nous aider à nous lancer.

Voici comment l’utiliser :

sql.match ( _requestedFieldName ; _matchFieldName ; _match )

• _requestedFieldName : le nom complet de la rubrique que l’on souhaite obtenir (respectivement potDeConfiture ::ID et potDeConfiture ::fruit dans les exemples précédents)
• _matchFieldName : le nom comple de la rubrique à tester (l’autre rubrique)
• _match : le critère d’égalité (« abricot » ou 348)

Le plus simple est de l’utiliser en combinaison avec la fonction ObtenirNomRubrique, afin de ne pas dépendre du nommage.

Exemple : sql.match( ObtenirNomRubrique ( PotDeConfiture ::ID ) ; ObtenirNomRubrique ( PotDeConfiture ::fruit ) ; "Abricot" )

renverra une liste des ID (séparés par des retours chariot) des Pots de confiture d’abricot

Ah ! un dernier détail. Afin de pouvoir observer la requête effectivement exécutée, une variable $sql.match.query sera déclarée.

Nom de la table Et puis, je ne résiste pas au plaisir de vous signaler cette autre fonction fm.basetable.name. Ceci résout un manque que je trouvais patent depuis de nombreuses années : la possibilité de connaître par calcul le nom de la table représentée par une occurrence de table.

Alors ? on s’y met ? Joyeux SQL à tous !