Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Dump Records from One Database Using Other's Schema

database record data pump database dump

  • Please log in to reply
No replies to this topic

#1 Luthfi

Luthfi

    CC Leader

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1320 posts
  • Programming Language:PHP, Delphi/Object Pascal, Pascal, Transact-SQL
  • Learning:C, Java, PHP

Posted 27 August 2013 - 10:32 PM

Overview

There is this thread in CodeCall asking if there is a tool in MySQL to dump records from one database to another one, but the design of both databases might be slightly different. I was intrigued since I had similar (but not exactly the same) problems in the past. In the thread I suggested to write a code that would export records from development database, USING production database schema/design. That way, we can expect no problem upon importing.

In my suggestion, I proposed to use loops to dump the records. One loop is iterating all tables in development. When a table exists both in development and production, the code would enter the second loop. The second loop iterates columns of the table. When a column exists in the table both in production and development, it will be stored in a collection. Just when entering the second loop, this column names collection must be cleared. When the second loop finished, we will enter the third and last loop ONLY IF there is at least one column name in the collection. When the column name is empty then we go to next iteration of the first loop, or simply exit the process.


PseudoCode










DevTables := GetDevelopmentTableNames;
ProdTables := GetProductionTableNames;
for i := 1 to CountOf(DevTables) do
begin
  TableName := DevTables[i];
  if TableName exists in ProdTables then
  begin
    DevColumns := GetDevelopmentColumnsOfTable(TableName);
    ProdColumns := GetProductionColumnsOfTable(TableName);
    ColumnNames.Clear;
    for j := 1 to CountOf(DevColumns) do
    begin
      ColumnName := DevColumns[j];
      if ColumnName exists in ProdColumns then
        ColumnNames.Add(ColumnName);
    end;
    if CountOf(ColumnNames) > 0 then
    begin
      DevTable := OpenDevelopmentTable(TableName);
      DevTable.GoToFirstRecord;
      while not DevTable.EndOfRecord do
      begin
        for j := 1 to CountOf(ColumnNames) do
          Write(DevTable.ColumnValues(ColumnNames[i]);
      end;
      WriteLn;
    end;
  end;
end;

in the demo project, the above pseudocode was implemented this way:






procedure TForm1.DumpDevRecordsWithProdSchema(ADest: TStrings);

  function CreateStrings: TStringList;
  begin
    Result := TStringList.Create;
    Result.Sorted := True;
    Result.Duplicates := dupIgnore;
  end;

var
  i, j: Integer;
  vQry: TADOQuery;
  vTable: string;
  vDevTables: TStrings;
  vProdTables: TStrings;
  vDevFields: TStrings;
  vProdFields: TStrings;
  vSelectCmds: TStrings;
  vSelectFields: TStrings;
begin
  ADest.BeginUpdate;
  try
    vDevTables := CreateStrings;
    vProdTables := CreateStrings;
    vDevFields := CreateStrings;
    vProdFields := CreateStrings;
    vSelectCmds := TStringList.Create;
    vSelectCmds.NameValueSeparator := '=';
    vSelectFields := TStringList.Create;
    try

      // get the table names in development
      cnnDev.Connected := True;
      cnnDev.GetTableNames(vDevTables);

      // get table names in production
      cnnProd.Connected := True;
      cnnProd.GetTableNames(vProdTables);

      for i := 0 to vProdTables.Count-1 do
      begin
        vTable := vProdTables[i];
        vSelectFields.Clear;
        // does the table also exist in development db, if not go to next iteration
        if vDevTables.IndexOf(vTable) < 0 then
          Continue;

        // get field names of selected table in development
        vDevFields.Clear;
        cnnDev.GetFieldNames(vTable, vDevFields);
        // get field names of selected table in production
        vProdFields.Clear;
        cnnProd.GetFieldNames(vTable, vProdFields);

        for j := 0 to vProdFields.Count-1 do
        begin
          if vDevFields.IndexOf(vProdFields[j]) < 0 then
            Continue;
          vSelectFields.Add(vProdFields[j]);
        end;

        // if no field selected, continue with next iteration
        if vSelectFields.Count = 0 then
          Continue;

        // now create select command from the available fields
        vSelectCmds.Add(vTable + '=SELECT ' + vSelectFields.CommaText + ' FROM ' + vTable);
      end;

      if vSelectCmds.Count=0 then Exit;

      vQry := TADOQuery.Create(nil);
      try
        vQry.Connection := cnnDev;

        // now do the data dump
        for i := 0 to vSelectCmds.Count-1 do
        begin
          vQry.Close;
          vQry.SQL.Text := vSelectCmds.ValueFromIndex[i];
          vQry.Open;

          if vQry.IsEmpty then
            Continue;

          GetInsertIntoCmds(vSelectCmds.Names[i], vQry, ADest);
        end;
      finally
        vQry.Free;
      end;

    finally
      vSelectFields.Free;
      vSelectCmds.Free;
      vDevTables.Free;
      vProdTables.Free;
      vDevFields.Free;
      vProdFields.Free;
    end;

  finally
    ADest.EndUpdate;
  end;
end;

Implementation of GetInsertIntoCmds is shown below. This method is responsible to render each record in ADataset into corresponding INSERT INTO commands. It uses the field information of the ADataset.






procedure TForm1.GetInsertIntoCmds(const ATableName: string; ADataset: TDataSet;
  ADest: TStrings);
var
  i: Integer;
  vFNames: string;
  vFValues: TStrings;
begin
  vFValues := TStringList.Create;
  try
    vFValues.StrictDelimiter := True;
    ADataset.GetFieldNames(vFValues);
    vFNames := 'INSERT INTO ' + ATableName + '(' + vFValues.CommaText + ') VALUES(';
    ADataset.First;
    while not ADataset.Eof do
    begin
      vFValues.Clear;
      for i := 0 to ADataset.FieldCount-1 do
        vFValues.Add(ADataset.Fields[i].AsString);

      ADest.Add(vFNames + Implode(vFValues, ',', '"') + ')');
      ADataset.Next;
    end;
  finally
    vFValues.Free;
  end;
end;

And here is the Implode method used in GetInsertIntoCmds.






function TForm1.Implode(AList: TStrings; const ADelimiter,
  AQuoteChar: Char): string;
var
  i, j: Integer;
  s: string;
begin
  Result := '';
  for i := 0 to AList.Count-1 do
  begin
    s := AList[i];
    j := 0;
    // adjust quote chars when found
    repeat
      j := PosEx(AQuoteChar, s, j);
      if j > 0 then
      begin
        Insert(AQuoteChar, s, j);
        Inc(j);
      end;
    until j < 1;

    Result := Result + ADelimiter + ' ' + AQuoteChar + s + AQuoteChar;
  end;
  System.Delete(Result, 1, 2);
end;

Demo Project
 
For this tutorial I have composed a simple demo project to show how the above codes in real action. You can download the source code and executable of this demo project through the link at the end of this tutorial.
 
Running the Demo
 
Upon running the demo project, you will get something like shown below.
 
 runtime_000.png
 
Now let's click on both Get Table Names buttons, and then click on both Addresses items in the Tables list boxes. And you will get the following. This is to show that Addresses tables in both database are slightly different. Addresses in development database has Address2 and Address3 columns, while production database has not.

 

runtime_010.png

 

 

Now click on Dump records button. And you will get this result.

 

runtime_020.png

 

Now that's it! You have dumped records from development database using production's scheme.

 

 

Additional note

 

  • In this tutorial I was using ADO library to access both databases. You might think that this fact makes the supported database only limited to ones that have ADO drivers. However ADO library also has ODBC driver, so if you can install and setup ODBC driver for your database (MySQL for example) then you can use demo project in this tutorial to dump records from your ODBC supported databases. Just need to adjust the connection strings of cnnDev and cnnProd to point to appropriate ODBC DSN-s.
  • I never actually tested the insert into commands generated by the demo project. While I believe they are valid, but I can't give any guarantee. You might need to do small adjustment when using it in actual project.

 

Attached File  Project1.zip   1.16MB   309 downloads

 


  • 0




Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download