Detailed analysis of your database structure

Did you know that you can retrieve very detailed information on a table, field, indexes, relation or even on the complete database structure? This kind of information is always very useful for analysis or introspection purposes. Of course, you could use the 4D Pack deprecated commands: _AP Get field infos and _AP Get table info. Another option is to use the SQL system tables.

None of them allows you to retrieve all information. So why use a plug-in or SQL when you could use a 4D command? The EXPORT STRUCTURE command returns the complete database structure definition in an XML tree. This command exists since 4D v14 R4.

For example, with this command, you can find out:

• whether a table is journalized,
• whether a field is a primary key,
• whether a trigger is associated with a field,
• whether a table or a field are exposed in 4D Mobile Service.

We provide you three methods to parse the XML tree and to save the result in an Object variable. Then, you can manipulate this object with the 4D object commands to analyze, display or do any other action that you want on a table, field, index, or relation.

The source code of each method as well as the complete example database (4D v16 version) is available:

Feel free to import these commands in your database, there are ready to use!

Information on tables and fields

The getTablesAndFields method parses the XML tree and retrieves the table and field information. The result is stored in an Object variable. This is an extract of an object in JSON format that contains the information on the “Table_1” table  and one of the fields named “ID“:

{
"id": "1",
"leave_tag_on_delete": "true",
"name": "Table_1",
"uuid": "0F8DD65FCF8E4CBB882B2FF15C9C8A12",
"trigger_insert": "true",
"comment": "Comments on my Table_1",
"displayable_fields_count": "14",
"color": {"alpha":"0","blue":"255","green":"255","red": "255"},
"coordinates": {"height":"355","left":"62","top":"70", "width":"152"},
"Field": [ {
	"autosequence": "true",
	"id": "1",
	"name": "ID",
	"not_null": "true",
	"type": "4",
	"unique": "true",
	"uuid": "1DC7531F29684D699D9FE9686AB71009",
	"index_ref": [ { "uuid": "7260EA8E18A1477A90F068F70754C384" } ],
	"pk": "true"
	} , {...} ]
}

Information on indexes

The getIndex method parses the XML tree and retrieves the index information. The result is stored in an Object variable. This is an extract of an object in JSON format; you can see the information on the “Index Name” index of the “[Table_1]Champ_Alp” field:

{
"kind": "regular",
"name": "Index Name",
"type": "7",
"uuid": "42E921F874EF44B4A2A08AD82CA53D91",
"field": [ {
	"name": "Champ_Alp",
	"uuid": "BC7FE443CAC344F993655D4EDE731D87",
	"table_name": "Table_1",
	"table_uuid": "0F8DD65FCF8E4CBB882B2FF15C9C8A12"
	} ]
}

Information on relations

The getRelations method parses the XML tree and retrieves the relation information. The result is also stored in an Object variable. This is an extract of the relation information between “[Table_1]ID” and “[Table_2]ID” in JSON format:

{
"auto_load_1toN": "false",
"auto_load_Nto1": "true",
"name_1toN": "Link_1_return",
"name_Nto1": "Link_1",
"uuid": "370180DABD8545D690CDC79E15D31215",
"choice_field": "0",
"entry_autofill": "false",
"entry_create": "false",
"entry_wildchar": "false",
"color": {"alpha":"0","blue":"255","green":"255","red":"255"},
"related_field": [ {
	"kind": "source",
	"name": "ID",
	"uuid": "1DC7531F29684D699D9FE9686AB71009",
	"table_name": "Table_1",
	"table_uuid": "0F8DD65FCF8E4CBB882B2FF15C9C8A12"
	} , {
	"kind": "destination",
	"name": "ID",
	"uuid": "B71D191358954A5783210E2447CD1A27",
	"table_name": "Table_2",
	"table_uuid": "6E23C70A973743B28809D9C3F3CC9060"
	} ]
}

Replace 4D Pack deprecated commands

You can also use the EXPORT STRUCTURE command to replace the 4D Pack deprecated commands. We provide you with two 4D methods which have been implemented in order to respond like the _AP Get field infos and _AP Get table info commands:

• Get table info: retrieves information on a table
• Get field info: retrieves information on a field