Three new commands were added to the FileMaker Developer Tool with the FileMaker 21.1. release. The FileMaker developer tool is available in the server’s “Database Server” {FileMaker Server Installation folder}\Database Server\FMDeveloperTool.exe on Windows or {FileMaker Server Installation folder}/Database Server/FMDeveloperTool on Linux. You can check the developer tool’s guide for more information about what it can do.
Determining the Size of Fields and Indexes
You can now get the exact size of a field index within a FileMaker file. Before you run these commands, make sure that the file is offline (not hosted and not open). You will need a FileMaker account with full access privileges to use the commands below.
You can always get information about which commands are available or how to use them by using the --help argument.
Description: Copying, cloning, saving as XML file, consistency checking, encrypting, decrypting, enabling Kiosk mode, removing Admin access, recovering a FileMaker Pro file and so on.
Copyright (C) 2024 Claris International Inc. All rights reserved.
Usage: FMDeveloperTool
--help (tool will print out the usage message)
--version (tool will only output the version number)
--copy <source_filename> <username> <password> [-target_filename | -t <path>] [-encryption_key | -e <key>]
--clone <source_filename> <username> <password> [-target_filename | -t <path>] [-encryption_key | -e <key>]
--copyCompress <source_filename> <username> <password> [-target_filename | -t <path>] [-encryption_key | -e <key>]
--copySelfContained <source_filename> <username> <password> [-target_filename | -t <path>] [-encryption_key | -e <key>]
--saveAsXML <source_filename> <username> <password> [-target_filename | -t <path>] [-encryption_key | -e <key>]
--enableEncryption <source_filename> <username> <password> [-target_filename | -t <path>] [-encryption_key | -e <key>] [-sharedID | -s <ID>] [-passcode | -p <passcode>] [-passcode_hint | -h <hint>] [-keep_open_storage | -o]
--removeEncryption <source_filename> <username> <password> [-target_filename | -t <path>] [-encryption_key | -e <key>]
--removeAdminAccess <source_filename> [-target_filename | -t <path>] [-encryption_key | -e <key>]
--enableKiosk <source_filename> [-target_filename | -t <path>] [-encryption_key | -e <key>]
--recover <source_filename> [-target_filename | -t <path>] [-encryption_key | -e <key>] [-generate | -g <rebuild | datablocks | asis>] [-skipSchema | -r] [-skipStructure | -l] [-rebuildIndex | -i <now | later | false>] [-keepCaches | -k] [-bypass | -b] [-username | -u <username>] [-password | -p <password>]
--checkConsistency <source_filename> [-encryption_key | -e <key>]
--renameFiles <target_folder> <username> <password> [-encryption_key | -e <key>] [-source_filelist | -sl <path1> <path2> ...] [-regex | -rg <regex>][-replace_text | -rt <text>]
--querySize <source_filename> <username> <password> [-encryption_key | -e <key>] [-target_tablename | -tt <tableName>] [-target_fieldname | -tf <fieldName>] [-query_index | -qi] [-exclude_container | -ec] [-size_unit | -su <units>]
--sortBySize <source_filename> <username> <password> [-encryption_key | -e <key>] [-target_tablename | -tt <tableName>] [-query_index | -qi] [-quantity | -qu <number>] [-exclude_container | -ec] [-size_unit | -su <units>] [-csv_format | -cf]
--uploadDatabases <host_name> <host_username> <host_password> [-database_pathlist | -dl <path1> <path2> ...] [-encryption_key | -e <key>] [-target_folder | -tf <path>] [-without_remote_container | -worc] [-automatically_open_db_off | -aodo] [-ignore_certificate | -ic]
Options can be applied to all commands:
[-verbose | -v] Verbose mode.
[-quiet | -q] Quiet mode.
[-force | -f] Overwrite existing file that has same path/file name when generating new file.
Command specific options:
<source_filename> File path of source FileMaker Pro file.
<username> Source file account user name. Default to 'Admin', if not entered, will use it to try to login.
<password> Source file account password. Default to blank, if not entered, will use no password to try to login.
<target_folder> Target folder path.
[-target_filename | -t <path>] File path of newly generated target FileMaker Pro file.
[-source_filelist | -sl <path1> <path2> ...] List of source file path or folder path which contains source FileMaker Pro files. (required)
[-encryption_key | -e <key>] EAR password of source file.
[-sharedID | -s <ID>] Shared ID of files encrypted at the same time. (required)
[-passcode | -p <passcode>] EAR password of target file. (required)
[-passcode_hint | -h <hint>] EAR password hint of target file.
[-keep_open_storage | -o] EAR option, if added, will not transfer open storage container into secure container, default is off.
[-generate | -g <rebuild | datablocks | asis>] Recover option, only one choice, default is rebuild.
[-rebuildIndex | -i <now | later | false>] Recover option, only one choice, default is now, can be turned off.
[-skipSchema | -r] Recover option, skip rebuild schema, default is off.
[-skipStructure | -l] Recover option, skip rebuild structure, default is off.
[-keepCaches | -k] Recover option, keep/delete cached settings, default is false which means delete cached settings.
[-bypass | -b] Recover option, bypass startup script and layout, requires admin privileges, if giving this option, users need to provide admin privileges username and password ([-username | -u <username>] [-password | -p <password>]), default is off.
[-regex | -rg] Regex for search and replace file names. (required)
[-replace_text | -rt] The replace text for regex replace file names. Default value is empty string.
[-target_tablename | -tt] The target table name.
[-target_fieldname | -tf] The target field name.
[-query_index | -qi] Whether to query/sort by field index size. If argument added, will query/sort by field index size instead of field data size. Note that for querySize command, this option has to be bound with --target_fieldName, it won't take effect if no target field name is provided. To see all fields index list, use sortBySize command.
[-quantity | -qu] The quantity of tables/fields/indexes showing in the report. If not entered, it will show all available tables/fields/indexes.
[-exclude_container | -ec] Exclude the embedded container fields' data size out of the total table size. If option is included, Remote container data size will always be excluded when calculating sizes with --querySize or --sortBySize command. This option only works when querying table size or sort by table size, will not work when querying specific field size or index size.
[-size_unit | -su] The size unit for query/sort tables/fields/indexes, can be "bytes", "kb", "mb", "gb", default is "bytes".
[-csv_format | -cf] If specified, output sortBySize results in csv format, then can choose to save output to a csv file if needed.
[-database_pathlist | -dl <path1> <path2> ...] List of databases to upload, can be folders, will only pick up fmp12 files when it is a folder. Note that if with encrypted databases, all encrypted databases should share the same EAR key, so that it works with the encryption key provided, otherwise people need to split them into multiple command lines, each providing the correct EAR key.
[-target_folder | -tf] The target place where users would like to upload the databases to, users should provide a path that is the same as what displayed in FMPro Upload to Host dialog, default is 'Databases/', secure folder is 'Secure/'(which requires encrypted databases), for sub-folder target path, put sub-folder name after root folder with slash at the end, e.g. 'Databases/Sample/'.
[-without_remote_container | -worc] Upload the database without remote container(if any), default is off.
[-automatically_open_db_off | -aodo] Automatically host the database on Server after upload, default is on.
[-ignore_certificate | -ic] Ignore SSL certificate verification, default is off.–querySize – This function can retrieve the size of data within a particular field and can provide the size of the index if needed. The help texts for this command’s arguments are shown below.
--querySize <source_filename> <username> <password>
[-encryption_key | -e <key>]
[-target_tablename | -tt <tableName>]
[-target_fieldname | -tf <fieldName>]
[-query_index | -qi]
[-exclude_container | -ec]
[-size_unit | -su <units>]
[-encryption_key | -e <key>] EAR password of source file.
[-target_tablename | -tt] The target table name.
[-target_fieldname | -tf] The target field name.
[-query_index | -qi] Whether to query/sort by field index size. If argument added, will query/sort by field index size instead of field data size. Note that for querySize command, this option has to be bound with --target_fieldName, it won't take effect if no target field name is provided. To see all fields index list, use sortBySize command.
[-exclude_container | -ec] Exclude the embedded container fields' data size out of the total table size. If option is included, Remote container data size will always be excluded when calculating sizes with --querySize or --sortBySize command. This option only works when querying table size or sort by table size, will not work when querying specific field size or index size.
[-size_unit | -su] The size unit for query/sort tables/fields/indexes, can be "bytes", "kb", "mb", "gb", default is "bytes".
For example, to get the total size in MB of data stored in the “Email” text field in the “Person” table:
> .\FMDeveloperTool.exe --querySize C:\Temp\Fake_Data.fmp12 USERNAME PASSWORD -target_tablename Person -target_fieldname Email -size_unit mb`
Field size of Person:Email is 1 mb.To get the total size in MB of data in “Person” table:
> .\FMDeveloperTool.exe --querySize C:\Temp\Fake_Data.fmp12 USERNAME PASSWORD -target_tablename Person -size_unit mb
Table size of Person is 19 mb.To get the size in MB of the index of the “Email” text field in the “Person” table with the index set to “Minimal”:
> .\FMDeveloperTool.exe --querySize C:\Temp\Fake_Data.fmp12 USERNAME PASSWORD -target_tablename Person -target_fieldname Email -query_index -size_unit mb
Field name: Person:Email Value index Size: 3(mb)The same text field with the index set to “All”:
Field name: Person:Email Value index Size: 3(mb) Word index Size: 1(mb)–sortBySize Return a sorted list of fields based on their size in descending order. You can choose to retrieve the index size or modify the number of fields to be returned. The help texts for this command’s arguments are shown below.
--sortBySize <source_filename> <username> <password>
[-encryption_key | -e <key>]
[-target_tablename | -tt <tableName>]
[-query_index | -qi]
[-quantity | -qu <number>]
[-exclude_container | -ec]
[-size_unit | -su <units>]
[-csv_format | -cf]
[-encryption_key | -e <key>] EAR password of source file.
[-target_tablename | -tt] The target table name.
[-query_index | -qi] Whether to query/sort by field index size. If argument added, will query/sort by field index size instead of field data size. Note that for querySize command, this option has to be bound with --target_fieldName, it won't take effect if no target field name is provided. To see all fields index list, use sortBySize command.
[-quantity | -qu] The quantity of tables/fields/indexes showing in the report. If not entered, it will show all available tables/fields/indexes.
[-exclude_container | -ec] Exclude the embedded container fields' data size out of the total table size. If option is included, Remote container data size will always be excluded when calculating sizes with --querySize or --sortBySize command. This option only works when querying table size or sort by table size, will not work when querying specific field size or index size.For example, to get the data size of each field in the “Person” table with CSV as the output format, sorted in descending order based on the size value:
> .\FMDeveloperTool.exe --sortBySize C:\Temp\Fake_Data.fmp12 USERNAME PASSWORD -target_tablename Person -size_unit mb -csv_format
FieldName,FieldSize,Units
"ID",2,mb
"Company_ID",2,mb
"Name_First",1,mb
"Address_Street",1,mb
"Email",1,mb
"Job_Title",1,mb
"zz_recModifyTimestamp",1,mb
"Address_City",0,mb
"zz_recModifyAccountName",0,mb
"Date_Birth",0,mb
"Address_State",0,mb
"Name_Last",0,mb
"Name_Middle",0,mb
"Address_Zip",0,mb
"IsOrg",0,mbTo get the size of each index in the table, sorted from largest to smallest:
> .\FMDeveloperTool.exe --sortBySize C:\Temp\Fake_Data.fmp12 USERNAME PASSWORD -target_tablename Person -size_unit mb -csv_format -query_index
FieldName,ValueIndexSize,Units,WordIndexSize,Units
"ID",5,mb,5,mb
"Email",3,mb,1,mb
"Address_Street",0,mb,0,mb
"Name_First",0,mb,0,mb
"Name_Middle",0,mb,0,mb
"Name_Last",0,mb,0,mb
"Company_ID",0,mb,0,mb
"IsOrg",0,mb,0,mb
"zz_recModifyTimestamp",0,mb,0,mb
"Date_Birth",0,mb,0,mb
"Address_City",0,mb,0,mb
"Address_State",0,mb,0,mb
"Address_Zip",0,mb,0,mb
"Job_Title",0,mb,0,mb
"zz_recCreateAccountName",0,mb,0,mb
"zz_recCreateTimestamp",0,mb,0,mb
"zz_recModifyAccountName",0,mb,0,mbNote that this command returns all the fields, even those without indexes.
To compare different types of UUID keys, I created four fields:
- uuid_text_value: Text field, populated with the output of
get(UUID). Index storage “Minimal” (value index). Language: English. - uuid_text_value_word: Text field, populated with the output of
get(UUID). Index storage “All” (word and value indexes). Language: English. - uuid_text_value_unicode: Text field, populated with the output of
get(UUID). Index storage “All” (word and value indexes). Language: Unicode. - uuid_number: Number field, populated with the output of
get(UUIDNumber). Language: English - uuid_number_unicode: Number field, populated with the output of
get(UUIDNumber). Language: Unicode.
Query to get the data size of the fields:
> .\FMDeveloperTool.exe --sortBySize C:\Temp\Fake_Data.fmp12 test test -target_tablename Log -size_unit mb -csv_format
FieldName,FieldSize,Units
"uuid_number_unicode",16,mb
"uuid_number",16,mb
"uuid_text_value",10,mb
"uuid_text_value_word",10,mb
"uuid_text_value_word_unicode",10,mbQuery to get the index size of the fields:
> .\FMDeveloperTool.exe --sortBySize C:\Temp\Fake_Data.fmp12 test test -target_tablename Log -size_unit mb -query_index -csv_format
FieldName,ValueIndexSize,Units,WordIndexSize,Units
"uuid_text_value_word_unicode",21,mb,21,mb
"uuid_text_value_word",21,mb,18,mb
"uuid_text_value",21,mb,0,mb
"uuid_number_unicode",9,mb,0,mb
"uuid_number",9,mb,0,mbThe FileMaker community has often discussed the different types of UUIDs. Here is a comparison table showing how 300,000 UUIDs affect file size, addressing the storage aspect of the discussion.
| Field Name | Language | Index Type | Data Size | Value Index Size | Word Index Size | Total Field Size |
|---|---|---|---|---|---|---|
| uuid_text_value | English | Minimal | 10 MB | 21 MB | 0 MB | 31 MB |
| uuid_text_value_word | English | All | 10 MB | 21 MB | 18 MB | 49 MB |
| uuid_text_value_word_unicode | Unicode | All | 10 MB | 21 MB | 21 MB | 52 MB |
| uuid_number | English | All | 16 MB | 9 MB | N/A | 25 MB |
| uuid_number_unicode | Unicode | All | 16 MB | 9 MB | N/A | 25 MB |
Uploading Database Files
You now have the capability to upload FileMaker databases directly to a FileMaker Server using the FileMaker Developer Tool. Previously, the methods available for uploading databases were manual file copying to a database folder, the Admin API, and FileMaker Pro.
Like on FileMaker Pro, this tool will run consistency checks before uploading the files to the server. The files are uploaded to {FMS Installation folder}\Data\Databases\UploadTemp_FMS before being written to the parent directory.
The help text for the new command --uploadDatabases is displayed below, along with the help text for its arguments:
--uploadDatabases <host_name> <host_username> <host_password>
[-database_pathlist | -dl <path1> <path2> ...]
[-encryption_key | -e <key>]
[-target_folder | -tf <path>]
[-without_remote_container | -worc]
[-automatically_open_db_off | -aodo]
[-ignore_certificate | -ic]
[-database_pathlist | -dl <path1> <path2> ...] List of databases to upload, can be folders, will only pick up fmp12 files when it is a folder. Note that if with encrypted databases, all encrypted databases should share the same EAR key, so that it works with the encryption key provided, otherwise people need to split them into multiple command lines, each providing the correct EAR key.
[-encryption_key | -e <key>] EAR password of source file.
[-target_folder | -tf] The target place where users would like to upload the databases to, users should provide a path that is the same as what displayed in FMPro Upload to Host dialog, default is 'Databases/', secure folder is 'Secure/'(which requires encrypted databases), for sub-folder target path, put sub-folder name after root folder with slash at the end, e.g. 'Databases/Sample/'.
[-without_remote_container | -worc] Upload the database without remote container(if any), default is off.
[-automatically_open_db_off | -aodo] Automatically host the database on Server after upload, default is on.
[-ignore_certificate | -ic] Ignore SSL certificate verification, default is off.To upload all the .fmp12 files from my local machine’s C:\Temp, I used the command below:
> .\FMDeveloperTool.exe --uploadDatabases fake_subdomain.soliantconsulting.com ADMIN_CONSOLE_USERNAME ADMIN_CONSOLE_PASSWORD -database_pathlist C:\Temp -target_folder 'FM_Databases/'
"test.fmp12": File was uploaded successfully.
"test_clone.fmp12": File was uploaded successfully.
Summary: All database files were uploaded successfully.With verbose output:
> .\FMDeveloperTool.exe --uploadDatabases fake_subdomain.soliantconsulting.com ADMIN_CONSOLE_USERNAME ADMIN_CONSOLE_PASSWORD -database_pathlist C:\Temp -target_folder 'FM_Databases/' -verbose
FMDeveloperTool 21.1.1.38 (09-25-2024)
Source files:
C:\temp\test.fmp12
C:\temp\test_clone.fmp12
Host name: "fake_subdomain.soliantconsulting.com"
Target upload folder: "FM_Databases/"
Start: Wed Oct 2 16:47:06 2024
*** Started consistency check, total of 66872 block(s) to check
*** Completed consistency check, checked 66872 block(s)
*** Started consistency check, total of 162 block(s) to check
*** Completed consistency check, checked 162 block(s)
"test.fmp12": File was uploaded successfully.
"test_clone.fmp12": File was uploaded successfully.
Summary: All database files were uploaded successfully.
End: Wed Oct 2 16:47:19 2024We hope you find these updates to the FileMaker Developer Tool helpful in your work!
I can’t find any official information online for the new subcommands and their options – can you ?
Official information about the new commands is available here and in the server release notes.