Database Reverse Engineering - Felix John COLIBRI.

• abstract : This project rebuilds the schema of a Database by analyzing all the .DFMs of the project
  
• key words : Database, Schema, .DFM, parser, reverse engineering
  
• software used : Windows XP, Delphi 6
  
• hardware used : Pentium 1.400Mhz, 256 M memory, 140 G hard disc
  
• scope : Delphi 1 to 2005 for Windows, Kylix
  
• level : Delphi developer
  
• plan :
  
  • Introduction
    
  • The Delphi Dfm Parser
    
  • Download the Sources
    

1 - Introduction

To recover or build from an existing application the schema, we basically have 3 options:

• analyze the data, by querying the Server
  
• analyze the SQL Scripts which were used to create the Base
  
• analyze the programs which manipulate the Base
  

The second only work if some Scripts were used, and if they present a good coverage of all the tables. We all know that in Delphi, we can build Tables by code, and this would not be in any Sql Script.

The third is also an imperfect solution, since the Delphi application may not manipulate all the tables in the Base. You can analyze the Delphi source code, looking for the tDataSet and tField descendents. This solution can also be used when you do not have the source code, and not the Database files: you can extract the .DFM from the .EXE (since the .DFM is a Windows Resource), and from this .DFM you can recover some information on the tables. We will now examine how this can be done.

2 - The Delphi Dfm Parser

2.1 - Assumptions

We will use our .DFM parser. We therefore also assume that the .DFM are in TEXT format. If they are in binary format, you can:

• either use the Delphi IDE and use the "save as txt" menu option from the Form's contextual menu
  
• or use the CONVERT.EXE located in the Delphi BIN folder directly from the command line or by using our .DFM binary to txt utility.
  

2.2 - The .DFM content

This sample tForm contained:

• a tTable linked to DBDEMO and with a TableName set to "ANIMALS.DBF", and all the persistent fields were created
  
• a tIbQuery:
  
  • we dropped a tIbDataBase, connected it to EMPLOYEE.GDB
    
  • we dropped a tIbTransaction and connected it to the tIbDatabase
    
  • we dropped a tIbQuery, connected it to the tIbDatabase, and set the SQL request to "SELECT * FROM CUSTOMER", and created the persistent fields
    

Here is the content of the corresponding .DFM (partial):

A quick analysis tells us that:

• for the tTable
  
  • the tTable.TableName contains the Server Table name
    
  • the tFields are nested within the tTable, and each tField contains the column name, the Delphi type and additional parameters (like Size)
    
• for the tIbQuery:
  
  • the tIbQuery.SQL.Strings contains the request
    
  • the Table name is nested in the SQL request (in the FROM clause)
    
  • there are some additional field types, like tIbStringField
    

2.3 - The extraction strategy

In addition:

• several tTable components might reference the same Table
  
• for the tQueries, the Table name is hidden (in the SQL) and the request might even correspond to several tables (a join)
  

The analyzer works like this:

• all the .DFM in a path are parsed
  
• for each .DFM
  
  • the complete c_dfm_object tree is built
    
  • the tree is then analyzed to find tDataSet components
    
  • for each tDataSet component, the key is extracted:
    
    • tTable.TableName
      
    • tQuery.SQL.Strings
      
  • all the tFields corresponding to this key are added in an object with this key
    

2.4 - The tDataSet list

• the key is the tTable.TableName or tQuery.SQL.Strings
  
• the fields are saved in a list of c_fields, containing each the name of the column and its type and parameters, if any.
  

 
animals.dbf 
    NAME            ftString    10 
    SIZE            ftSmallint   0 
    WEIGHT          ftSmallint   0 
    AREA            ftString     0 
    BMP             ftBlob       0 
select * from CUSTOMER 
    CUST_NO         ftInteger    0 
    CUSTOMER        ftUnknown    0 
    CONTACT_FIRST   ftUnknown    0 
    CONTACT_LAST    ftUnknown    0 
    PHONE_NO        ftUnknown    0 
    ADDRESS_LINE1   ftUnknown    0 
    ADDRESS_LINE2   ftUnknown    0 
    CITY            ftUnknown    0 
    STATE_PROVINCE  ftUnknown    0 
    COUNTRY         ftUnknown    0 
    POSTAL_CODE     ftUnknown    0 
    ON_HOLD         ftUnknown    0 

2.5 - Table Relationships

This is embodied in Delphi Master / Detail relationships. Basically there are 2 techniques to link 2 Tables:

• for tTables
  
  • the tDetailTable.MasterSource property contains the MasterDataSource value
    
  • the tDetailTable.MasterFields property contains a semi-colon separated list of the linking fields
    
• for tQueries:
  
  • the tDetailQuery.DataSource property contains the MasterDataSource value
    
  • the tDetailQuery.SQL contains a parametrized query specifying which columns are used for the linking fields
    

Here is the corresponding .DFM:

Notice that

• for the tTable detail
  
  • for the tDetailTable "Table2", the MasterDataSource value points to "DataSource1", and this tDataSource.DataSet points to the master tTable: "Table1"
    
  • the tDetailTable.IndexFieldNames "OrderNo" and tDetailTable.MasterFields "OrderNo" tell us which are the linked columns
    
• for tQueries:
  
  • for the tDetailQuery "Query1", the DataSource property points to "DataSource1", and this tDataSource.DataSet points to the master tTable: "Table1"
    
  • the tDetailQuery.SQL contains the parametrized query with both link colums ("OrderNo" for "Items.Db" and ":OrderNo" for the master)
    

• we have to go thru the masterSource:
       tDetailDataset -> tMasterDataSource -> tMasterDataSet
  
• we have to transform the link into Table links. All .Dfm links use the component names:
       "Table2" -> "DataSource1"-> "Table1"
  and NOT
       "Items.Db" -> -> "Orders.Db"
  This causes a problem, since when we analyze several tForms, the key we are using for each c_dataset is the Table name ("Orders.Db"), which might correspond to several tTable.Names ("SortOrdersByDate", "Orders", "Table15" "FindMaxOrderNumber" etc).
  Therefore the link analysis must be performed on a form by form basis, temporarily attaching the list of tDataSet names to each c_dataset in order to find the master detail links.
  

2.6 - The c_dataset list

• it is a list of c_dataset
  
• each c_dataset
  
  • has as key either the Table name (ORDERS.DB) or the Sql request (SELECT * FROM "orders.db")
    
  • contains a list of c_field, each field having a name, a tFieldType and some parameters
    
  • contains a list of tDataSet.Name used to find the master detail links during a single .DFM analysis
    
  • has a possible c_master_dataset_ref link and the link column names
    

Here are the CLASS definitions:

The c_dataset CLASS has two descendent, a c_dataset_table and a c_dataset_query. This allows to adapt the differences in the tDataSet types.

2.7 - The extraction

• the definition of the c_analyze_db class is :

  c_analyze_db= class(c_basic_object)                  m_c_dataset_list_ref: c_dataset_list;                  Constructor create_analyze_db(p_name: String);                  procedure analyze(p_c_dfm_object: c_dfm_object);                end; // c_analyze_db

• the method which adds c_dataset elements is:

  procedure c_analyze_db.analyze(p_c_dfm_object: c_dfm_object);   // ...   begin // analyze     analyze_recursive(0, p_c_dfm_object);     resolve_master_detail;   end; // analyze

• and here we show how we fill the tTable part:

  procedure analyze_recursive(p_level: Integer; p_c_dfm_object: c_dfm_object);   procedure add_fields(p_c_dfm_object_list: c_dfm_object_list; p_c_dataset: c_dataset);     var l_dfm_object_index: Integer;         l_field_type: tFieldType;     begin       with p_c_dfm_object_list do         for l_dfm_object_index:= 0 to f_dfm_object_count- 1 do           with f_c_dfm_object(l_dfm_object_index) do           begin             l_field_type:= f_field_type_name_to_field_type(m_name);             if l_field_type in [ftString]               then p_c_dataset.f_c_add_unique_field(                  f_find_property_value('FieldName'),                  l_field_type,                  f_string_to_integer(f_find_property_value('Size')))               else p_c_dataset.f_c_add_unique_field(                  f_find_property_value('FieldName'),                  l_field_type, 0);           end;     end; // add_fields   procedure analyze_table;     var // l_property_index: Integer;         l_table_name: String;         l_index_of_table: Integer;         l_c_dataset_table: c_dataset_table;     begin       with p_c_dfm_object do       begin         l_table_name:= f_find_property_value('TableName');         if l_table_name<> ''           then begin               l_index_of_table:= m_c_dataset_list_ref.f_index_of(l_table_name);               if l_index_of_table< 0                 then begin                     l_c_dataset_table:= c_dataset_table.create_dataset_table(l_table_name,                         m_c_dataset_list_ref);                     m_c_dataset_list_ref.add_dataset(l_table_name, l_c_dataset_table);                   end                 else l_c_dataset_table:= c_dataset_table(m_c_dataset_list_ref.f_c_dataset(l_index_of_table));               l_c_dataset_table.m_c_component_name_list.Add(m_object_name);               // -- check whether "DataSource"               l_c_dataset_table.m_master_datasource:= f_find_property_value('MasterSource');               l_c_dataset_table.m_masterfields:= f_find_property_value('MasterFields');               add_fields(m_c_dfm_object_list, l_c_dataset_table);             end           else display('  NO_TABLE_NAME');       end; // with p_c_dfm_object     end; // analyze_table   procedure analyze_query;     begin       // ...     end; // analyze_query   procedure recurse;     begin       // ...     end; // recurse   begin // analyze_recursive     with p_c_dfm_object do     begin       if (LowerCase(m_name)= 'ttable') or (m_name= 'TIBTable')         then analyze_table else       if (m_name= 'TQuery') or (LowerCase(m_name)= 'tibquery')         then analyze_query         else recurse;     end; // with p_c_dfm_object   end; // analyze_recursive

• and the method which finds the Master Detail links:

  procedure resolve_master_detail;     // -- if a tTable or tQuery reference a datasource, find its associated tDataset   var l_dataset_index: Integer;       l_c_dfm_datasource: c_dfm_object;       l_master_dataset: String;       l_c_dfm_master_dataset: c_dfm_object;       l_dataset_name: String;   begin     with m_c_dataset_list_ref do       for l_dataset_index:= 0 to f_dataset_count- 1 do         with f_c_dataset(l_dataset_index) do           if m_master_datasource<> ''             then begin                 if not m_did_resolve                   then begin                       // -- search this tDataSource in the .DFM                       with p_c_dfm_object do                       begin                         l_c_dfm_datasource:= m_c_dfm_object_list.f_c_find_by_dfm_object_name(m_master_datasource);                         if l_c_dfm_datasource<> Nil                           then begin                               // -- get the "DataSet" property value                               l_master_dataset:= l_c_dfm_datasource.f_find_property_value('DataSet');                               // -- now find the Master Dataset                               l_c_dfm_master_dataset:=                                    m_c_dfm_object_list.f_c_find_by_dfm_object_name(l_master_dataset);                               if l_c_dfm_master_dataset<> Nil                                 then begin                                     l_dataset_name:= l_c_dfm_master_dataset.m_object_name;                                     m_c_master_dataset_ref:=                                           m_c_dataset_list_ref.f_c_find_by_dataset_component_name(l_dataset_name);                                   end;                             end;                       end;                       // -- do not try to resolve in another form                       m_did_resolve:= True;                     end;               end;   end; // resolve_master_detail

  
  

2.8 - The project

2.9 - Mini Manual

	copy the DataMod.Dfm from the Mastapp demo project (in Program Files\Borland\Delphi\demos\db\) or any other .DFM containing some tDatasets.
	click Datamod.Dfm in the fFileListBox.
	this parses the .Dfm.
	you may visualize the c_dfm_object tree by clicking on "display_object_tree_"
	now the interesting part: to create and fill the c_dataset list, click on "extract_"
	the c_dataset list is filled using the last c_dfm_object tree
	to view the extracted datasets click "display_list_". check "Fields_" before the list if you wish to see all the fields
	to view only the Sql requests, click "extract_sql_"

	navigate to the directory where the .DFMs are located
	check "extract__"
	click "all_dir_" or "all_dir_recursive_"
	display with "display_list_" or extract the SQL with "extract_sql_"

2.10 - Schema display

• for the tTable (without the detail of the fields):

  NEXTCUST      =NextCust PARTS      =Parts VENDORS      =Vendors ORDERS      =Orders OrdByCust      ^CustMasterSrc -> CUSTOMER[CustNo] CUSTOMER      =CustByOrd Cust CustByComp ITEMS      =Items      ^OrdersSource -> ORDERS[OrderNo] NEXTORD      =NextOrd EMPLOYEE      =Emps

• for the tQueries (showing only the Sql requests):

  SELECT *   FROM parts   WHERE (parts.OnOrder > parts.OnHand)   SELECT MAX (ItemNo)   FROM Items   WHERE OrderNo = :OrderNo   SELECT Customer.CustNo, Customer.Company, Customer.LastInvoiceDate, Customer.Phone   FROM customer   WHERE (customer.LastInvoiceDate >= :FromDate)       AND (customer.LastInvoiceDate <= :ToDate)   SELECT *   FROM customer   ORDER BY LastInvoiceDate DESCENDING   SELECT *   FROM orders   WHERE (SaleDate >= :FromDate)       AND (SaleDate <= :ToDate)   ORDER BY SaleDate

  
  

• Customer -> Orders
  
• Items -> Orders
  

Anyway we can produce a simple diagram showing the Tables used by MastApp:

Note that this diagram could be automatically constructed using the extracted information and a topological sort (cf Niklaus WIRTH) on the Master / Detail relations.

2.11 - What is Missing ?

• we have limited our extraction to some tDataSet descendents: tTable, tQuery and tIbQuery. You can easily add other if they need special handling
  
• similarily we only handled some tFieldTypes, and did not spend much time on examining the different parameter types. For instance the tIbStringField is not recognized
  
• other valuable informations about tDataSets could be extracted: constraints, indexes etc
  
• the tQuery tDataSets could add some information to the Tables, but this would require analyzing the detail of the SQL request (which table, which columns etc). It so happened that after launching the tool on our customer's project, no significant information could be gained from such information (like in the above MastApp example). So we did not make any development in this area
  

• first of all, the Delphi project we are analyzing might only manipulate a small part of the Tables of a Base
  
• and for each Table, only the fields relevant to the Delphi Project are analyzed. Some other fields might be present in the Tables.
  
• the extraction depends on PERSISTENT fields. If the programmer used hard code access (tDataSet.Fields[n] or tDataSet.FieldByName('xxx')) this will not be extracted. A much more difficult .PAS extraction would be required. The same is true if the programmer created dynamic tables (tTable.CreateTable)
  
• the .DFM also converts all field formats into a Delphi common format. For instance, the numeric type are handled by Delphi with tIntegerField, tFloatFields etc, but the Server might have native numeric types which do not exactly match the Delphi types. This is the case, for instance, for xBase files. The same goes with Booleans or Dates, which are not present in all Database native types.
  However most Sql Server engines (Oracle, Sql Server, Interbase) now all use 16 bit, 32 bit integers, single and double floats, strings of some kinds, and normalized dates, which all are faithfully represented by Delphi field types. So using only the Delphi field types is not that risky.
  

3 - Download the Sources

• reverse_database.zip: the project with the parser and the data structure (51 K)
  
• master_detail_sample.zip: the two sample tForms (16 K)
  

• the main program (.DPR, .DOF, .RES), the main form (.PAS, .DFM), and any other auxiliary form
  
• any .TXT for parameters
  
• all units (.PAS) for units
  

• are self-contained: you will not need any other product (unless expressly mentioned).
  
• can be used from any folder (the pathes are RELATIVE)
  
• will not modify your PC in any way beyond the path where you placed the .ZIP (no registry changes, no path creation etc).
  

• create or select any folder of your choice
  
• unzip the downloaded file
  
• using Delphi, compile and execute
  

As usual:

• please tell us at fcolibri@felix-colibri.com if you found some errors, mistakes, bugs, broken links or had some problem downloading the file. Resulting corrections will be helpful for other readers
  
• we welcome any comment, criticism, enhancement, other sources or reference suggestion. Just send an e-mail to fcolibri@felix-colibri.com.
  
• or more simply, enter your (anonymous or with your e-mail if you want an answer) comments below and clic the "send" button

  Name :
  E-mail :
  Comments * :

• and if you liked this article, talk about this site to your fellow developpers, add a link to your links page ou mention our articles in your blog or newsgroup posts when relevant. That's the way we operate: the more traffic and Google references we get, the more articles we will write.
  

4 - Conclusion

5 - Other Papers with Source and Links

6 - The author