Database structuur opvragen met executeSQL (tip)

[hans erik]

Kwam op Technet een leuke link tegen, alweer uit 2012:

 

http://www.databuzz.com.au/using-executesql-to-query-the-virtual-schemasystem-tables/

 

Maakt gebruik van een undocumented feature van de FileMaker SQL engine. In de verborgen tabellen 'FileMaker_Tables' en FileMaker_Fields' houdt FileMaker informatie bij over de aanwezige Table Occurrences, Base Tables en velden.

 

 

Probeer dit bijvooorbeeld:

executeSQL( "SELECT * FROM FileMaker_Tables" ; " " ; "¶" )

 

Erg handig omdat je bijvoorbeeld snel een lijst van velden en basetables kunt samenstellen. De designfunctie FieldNames ( ) werkt bijvoorbeeld altijd op basis van een layout en geeft alleen de velden op die layout weer (en ook alle gerelateerde velden op die layout).

Deze executeSQL vult dat dus heel mooi aan.

 

Maar als je zoals ik vaak met data separation werkt, wat is dan de handigste methode om ook de fysieke bestandsnaam erbij te krijgen?

 

Bijvoorbeeld: SELECT DISTINCT BaseTableName FROM FileMaker_Tables

 

levert je de namen van de basetables op. Maar als een bestand bijvoorbeeld 5 Table Occurrences heeft (A, B, C, D en E), en D, E en F bevinden zich in een andere file, zie ik dat niet in het lijstje terug. De FileMaker_Tables bevat gewoon voor elke TOC een entry, zonder vermelding van de fysieke locatie.

[peterS]

goeie tip Hans Erik,

en als je meer dan 1 datafile hebt dan sorteert de lijst op volgorde van elke data file, kan ook handig zijn.

[menno]

op filemakerhacks.com heeft Kevin Frank met Beverly Voth samen ook in 2012 een artikel daarover gepubliceerd. Beverly heeft korte tijd later een pdf van een missing-fm-sql-manual gepubliceerd. Dat kan je downloaden op: http://www.kevinfrank.com/fmh/The-Missing-FM-12-ExecuteSQL-Reference.pdf en dat is best een handig boekwerkje, op pagina 20 worden dezelfde meta-functies kort besproken, maar lang niet zo uitgebreid

[Administrator]

Een beetje spijtig is dat het FileMaker een beetje ontbreekt aan consistente terminologie.

- table

- base table

- table occurrence

FileMaker gebruikt deze begrippen hopeloos door mekaar. Met het begrip "tables" wordt zowel verwezen naar de fysieke tabellen als de occurrences.

Voor alle duidelijkheid: met het BaseTableName veld van FileMaker_Tables wordt de naam van de fysieke tabel vermeld. Deze kan je NIET gebruiken in SQL queries, tenzij die toevallig overeenkomen met de occurrence die meestal automatisch mee aangemaakt wordt bij de aanmaak van de fysieke tabel, en ook automatisch zal hernoemd worden als deze gelijk is aan de fysieke tabelnaam als deze hernoemd wordt.

Heel wat FileMaker developers maken gebruik van het begrip PTO, dat staat voor Primary Table Occurrence. Dat is die occurrence die automatisch aangemaakt wordt. Deze heeft de laagste TableID. Als de PTO verwijderd wordt, en er bestaan nog andere occurrences, dan zal de occurrence met de laagste tableID de PTO worden. De PTO is ook de default start referentie in een calculatie die je nieuw aanmaakt in de BaseTable, en wordt de referentie als je een calculatieveld kopieert en plakt in dezelfde tabel ( en dit laatste is een long standing bug ).

 

In FileMaker_Fields wordt er verwezen naar de TableName, en dat is de occurrence van dat veld, en niet de BaseTableName. Als je 10 occurrences hebt van een BaseTable, dan zullen je velden dus 10 keer voorkomen in FileMaker_Fields, wat een beetje onhandig is als je alleen je BaseTables en bijhorende velden wil documenteren.

De designfunctie FieldNames ( ) werkt bijvoorbeeld altijd op basis van een layout en geeft alleen de velden op die layout weer

Dat is niet helemaal correct. FieldNames( fileName ; layoutName ) geeft de velden van de layout als deze bestaat, en de velden van de baseTable als de layout niet bestaat. Het feit dat "layoutName" in de functie prototype gebruikt wordt zet ons natuurlijk ( nogmaals ) op het verkeerde spoor.

[hans erik]

Dat is niet helemaal correct. FieldNames( fileName ; layoutName ) geeft de velden van de layout als deze bestaat, en de velden van de baseTable als de layout niet bestaat.

 

Dat klopt.

 

Ook jammer is dat FMPA de ID's van Table Occurrences, Velden en Layouts e.d. niet optioneel gewoon in de dialogs laat zien. Moet toch niet zo moeilijk te programmeren zijn. En een import 'match by ID' is dan een logische vervolgstap. FMP14? Of wordt het nu FMP15?