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

4D methodThe 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

4D methodThe 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

4D methodThe 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

4D methodYou 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

 

Vanessa Talbot
• Product Owner •Vanessa Talbot joined 4D Program team in June, 2014. As a Product Owner, she is in charge of writing the user stories then translating it to functional specifications. Her role is also to make sure that the feature implementation delivered is meeting the customer need.Since her arrival, she has worked to define key features in 4D. She has worked on most of preemptive multi-threading new features and also on a very complex subject: the new architecture for engined application. Vanessa has a degree from Telecom Saint-Etienne. She began her career at the Criminal Research Institute as a developer for the audiovisual department. She has also worked in media and medical fields as expert in technical support, production as well as documenting new features.