[Feature Request] Renaming columns

[Cobollatin]

I want to use TablesToBeExportedDic with a query that contains an alias, e.i. something like this "select ID as 'ID_ALIAS` from table;", and the script should result "insert into table (ID_ALIAS, ..."

I am currently post-processing the script, but I think it's a good feature. Is there an existing workaround for this issue? If the feature solves a valid user case, I don't mind working on its implementation.

[adriancs2]

I have tried a quick modification to produce the result as you described. Here is the sample project:

ConsoleApp2.zip

This project uses a modified MySqlBackup.NET.

The following are the changes:

File: /MySqlBackup/MySqlBackup.cs

In this method:

private string Export_GetInsertStatementHeader(RowsDataExportMode rowsExportMode, string tableName, MySqlDataReader rdr)

replace the following line:

if (_database.Tables[tableName].Columns[_colname].IsGeneratedColumn)
	continue;

with:

if (_database.Tables[tableName].Columns.Contains(_colname))
{
	if (_database.Tables[tableName].Columns[_colname].IsGeneratedColumn)
		continue;
}

In this method:

private string Export_GetValueString(MySqlDataReader rdr, MySqlTable table)

replace the following line

if (table.Columns[columnName].IsGeneratedColumn)
	continue;

with:

if (table.Columns.Contains(columnName))
{
	if (table.Columns[columnName].IsGeneratedColumn)
		continue;
}

in this method:

private string Export_GetValueString(MySqlDataReader rdr, MySqlTable table)

replace the following line:

var col = table.Columns[columnName];

with:

MySqlColumn col = null;

if (table.Columns.Contains(columnName))
{
	col = table.Columns[columnName];
}

[adriancs2]

There is one issue, if you replace the column name by an alias name, then the CREATE TABLE sql statement will no longer valid.

for example:

This original table structure:

CREATE TABLE if not exists `people` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `tel` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

will no longer fit the generate INSERT with alias column name. The following INSERT will fail, since there is no column name person_name.

INSERT INTO `people`(`id`,`person_name`,`tel`) VALUES
(1,'Sam','1111'),
(2,'Foo','2222'),
(3,'Bar','3333');

But I assume that the destination database that you are going to import to, will have the column name person_name existed.

[adriancs2]

In this method:

private string Export_GetValueString(MySqlDataReader rdr, MySqlTable table)

The MySqlColumn

MySqlColumn col = null;

if (table.Columns.Contains(columnName))
{
	col = table.Columns[columnName];
}

will be passed into another method:

QueryExpress.ConvertToSqlFormat(ob, true, true, col, ExportInfo.BlobExportMode)

The col supplies the info for handling non-standard DateTime value. For example in MySql, there are date, time, null date, null time, and different time fraction... which is not compatible with C# DateTime data type.

These non-compatible Date or Time values will be converted into MySqlDateTime, here, the MySqlColumn value will be needed

else if (ob is MySqlDateTime mdt)
{
    if (mdt.IsValidDateTime)
    {
        DateTime dtime = mdt.GetDateTime();

        if (wrapStringWithSingleQuote)
            sb.AppendFormat("'");

        if (col.MySqlDataType == "datetime")
            sb.AppendFormat(dtime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));
        else if (col.MySqlDataType == "date")
            sb.AppendFormat(dtime.ToString("yyyy-MM-dd", _dateFormatInfo));
        else if (col.MySqlDataType == "time")
            sb.AppendFormat("{0}:{1}:{2}", mdt.Hour, mdt.Minute, mdt.Second);
        else
            sb.AppendFormat(dtime.ToString("yyyy-MM-dd HH:mm:ss", _dateFormatInfo));

        if (col.TimeFractionLength > 0)
        {
            sb.Append(".");
            sb.Append(((MySqlDateTime)ob).Microsecond.ToString().PadLeft(col.TimeFractionLength, '0'));
        }

        if (wrapStringWithSingleQuote)
            sb.AppendFormat("'");
    }
    else
    {
        if (wrapStringWithSingleQuote)
            sb.AppendFormat("'");

        if (col.MySqlDataType == "datetime")
            sb.AppendFormat("0000-00-00 00:00:00");
        else if (col.MySqlDataType == "date")
            sb.AppendFormat("0000-00-00");
        else if (col.MySqlDataType == "time")
            sb.AppendFormat("00:00:00");
        else
            sb.AppendFormat("0000-00-00 00:00:00");

        if (col.TimeFractionLength > 0)
        {
            sb.Append(".".PadRight(col.TimeFractionLength, '0'));
        }

        if (wrapStringWithSingleQuote)
            sb.AppendFormat("'");
    }
}

but since you are using alias column name, the MySqlBackup.NET library unable to guess the alias column name belongs to which column in MySQL table.

In this case (in the occurance of if you do have these incompatible datetime values issues), another error (exception) will occur, where the MySqlColumn is null.

However, there is another workaround to avoid this, which is by appending a connection string option:

convertzerodatetime=true;

example:

server=localhost;user=root;pwd=1234;database=test1;convertzerodatetime=true;

or using the ConnectionStringBuilder:

MySqlConnectionStringBuilder consb = new MySqlConnectionStringBuilder()
{
    Server = "localhost",
    UserID = "root",
    Password = "1234",
    Database = "test1",
    ConvertZeroDateTime = true
};

using (MySqlConnection conn = new MySqlConnection(consb.ToString()))
{
}

By specifying ConvertZeroDateTime=true, all non-standardized Date or Time value in MySQL will all be converted into DateTime.MinValue in C#.

This will by pass the need to refer to MySqlColumn to get further info about the date time behaviour.

[Cobollatin]

But I assume that the destination database that you are going to import to, will have the column name person_name existed.

That's indeed the case.

This will by pass the need to refer to MySqlColumn to get further info about the date time behaviour.

Imo it should be handled by the library.

Will those changes come with a release?

[adriancs2]

Imo it should be handled by the library.

It requires user input to match the person_name to the original name.

It can be something like

mb.ExportInfo.MatchAliasColumns(table_name, ori_column_name, alias_column_name);

I can add it into the next release. The next release date is unscheduled at the moment. You may use the modified version of MySqlBackup.NET attached in previous reply (above).

[Cobollatin]

Alright, thank you. Feel free to close the issue at your discretion.