Regarding code structure

Coordinator
Oct 22, 2012 at 2:54 PM

In my opinion you are hard-coding too much SQL dialect specific methods in your base class.

I'd suggest creating SQL invariant (abstract) classes like Database, Table, Field, StoredProcedure, etc., that would hold abstract info about database structure.

Then use an interface for SQL dialect dependent code (e.g. ISqlMethod) and implement it for particular dialects.

Then in your base class you can instantiate adequate class and  use it.

This approach makes it much easer  to implement not only other SQL servers but also different strategies for dumping data (e.g. XML dump that would be very useful for data migration between different servers), database structure upgrade, etc.

I could write the abstract classes combining my code (that I've sent you) and yours. But my native language is VB, i.e. it's very uncomfortable for me to code C#.

Coordinator
Oct 24, 2012 at 12:48 AM

Hi, one of our team member - asomarribasd has suggested the similar idea as yours a few months ago - have classes for Database, tables, rows, etc.

Consequences of that suggestion, I do have built classes for Database and Table. However, I'm not sure if this has meet the initial plan of building the class or not. 

As per your suggestion, the current base class/code seems to have a large potential to extend to a more flexible structure. It will make the base class/code easier to adopt more various method for exporting MySQL database. I can understand the concept of building classes for Stored Procedure, Functions, Triggers, Events and View, and this will be implemented in the next release.

Example of classes, is this what you meant?

public class StoredProcedure
{
    public string Name { get; set; }
    public string Definer { get; set; }
    public DateTime Modified { get; set; }
    public DateTime Created { get; set; }
    public string SecurityType { get; set; }
    public string Comment { get; set; }
    public string CharacterSetClient { get; set; }
    public string CollationConnection { get; set; }
    public string DatabaseCollation { get; set; }
    public string SqlMode { get; set; }
    public string CreateProcedureSql { get; set; }
}

public class Function
{
    public string Name { get; set; }
    public string Definer { get; set; }
    public DateTime ModifiedDate { get; set; }
    public DateTime CreatedDate { get; set; }
    public string SecurityType { get; set; }
    public string Comment { get; set; }
    public string CharacterSetClient { get; set; }
    public string CollationConnection { get; set; }
    public string DatabaseCollation { get; set; }
    public string SqlMode { get; set; }
    public string CreateFunctionSql { get; set; }
}

public class Triggers
{
    public string Trigger { get; set; }
    public string Event { get; set; }
    public string Table { get; set; }
    public string Statement { get; set; }
    public string Timing { get; set; }
    public string SqlMode { get; set; }
    public string Definer { get; set; }
    public string CharacterSetClient { get; set; }
    public string DatabaseCollation { get; set; }

}

public class Event
{
    public string Name { get; set; }
    public string Definer { get; set; }
    public string TimeZone { get; set; }
    public string Type { get; set; }
    public string IntervalValue { get; set; }
    public string IntervalField { get; set; }
    public DateTime Starts { get; set; }
    public DateTime Ends { get; set; }
    public string Status { get; set; }
    public string Originatro { get; set; }
    public string CharacterSetClient { get; set; }
    public string CollationConnection { get; set; }
    public string DatabaseCollation { get; set; }
    public string SqlMode { get; set; }
    public string CreateEventSql { get; set; }
}

public class View
{
    public string Name { get; set; }
    public string CreateSql { get; set; }
}

public class Row
{
    List<Data> lstData { get; set; }
    Dictionary<string, Data> dicData { get; set; }

    public string SqlValueString { get; set; }
    public string SqlInsertHeader { get; set; }
    public string SqlFullInsert { get; set; }
    public string SqlFullReplace { get; set; }
    public Table TableInfo { get; set; }

    public Data this[int index]
    {
        get
        {
            return lstData[index];
        }
        set
        {
            lstData[index] = value;
        }
    }

    public Data this[string fieldName]
    {
        get
        {
            return dicData[fieldName];
        }
        set
        {
            dicData[fieldName] = value;
        }
    }
}

public class Column
{
    public string Field { get; set; }
    public string Type { get; set; }
    public string Collation { get; set; }
    public bool Null { get; set; }
    public string Key { get; set; }
    public string Default { get; set; }
    public string Extra { get; set; }
    public string Privileges { get; set; }
    public string Comment { get; set; }
}

public class Data
{
    public string MySqlDataType { get; set; }

    object Value = null;

    public Type TypeInfo
    {
        get
        {
            return Value.GetType();
        }
    }

    public string SqlString
    {
        get
        {
            // example
            return Value + "";
        }
    }
}

But I'm not sure how to implement ISqlMethod. You can write the abstract classes too and we'll see what we can do.

You have already send me the codes? but I didn't received it. can you resend it again? thanks :)

It is ok if your code is in VB. I'll try to convert it.

Coordinator
Oct 24, 2012 at 10:41 AM
adriancs wrote:

But I'm not sure how to implement ISqlMethod.

Example:

 

Public Interface ISqlMethods

    Function GetSqlMethods(ByVal ConnectionString As String) As ISqlMethods

    Function GetTableList() As List(Of String)

    Function GetTable(ByVal TableName As String) As Table

End Interface

Public Class MySqlMethods
    Implements ISqlMethods, IDisposable

    Private _CurrentConnection As MySqlConnection
    Private _CurrentCommand As MySqlCommand
    Private _ConnectionString As String

    Private ReadOnly Property CurrentConnection() As MySqlConnection
        Get
            If _CurrentConnection Is Nothing Then _
                _CurrentConnection = New MySqlConnection(_ConnectionString)
            If _CurrentConnection.State <> ConnectionState.Open Then _CurrentConnection.Open()
            Return _CurrentConnection
        End Get
    End Property

    Private ReadOnly Property CurrentCommand() As MySqlCommand
        Get
            If _CurrentCommand Is Nothing Then
                _CurrentCommand = New MySqlCommand()
                _CurrentCommand.Connection = CurrentConnection
            End If
            Return _CurrentCommand
        End Get
    End Property


    Private Sub New()

    End Sub

    Private Sub New(ByVal nConnectionString As String)
        _ConnectionString = nConnectionString
    End Sub


    Public Function GetSqlMethods(ByVal ConnectionString As String) As ISqlMethods _
        Implements ISqlMethods.GetSqlMethods
        Return New MySqlMethods(ConnectionString)
    End Function

    Friend Function GetTableList() As System.Collections.Generic.List(Of String) _
        Implements ISqlMethods.GetTableList

        Dim result As List(Of String)

        Using dTables As New DataTable

            CurrentCommand.CommandText = "SHOW FULL TABLES WHERE Table_type LIKE 'BASE TABLE';"

            Using da As New MySqlDataAdapter(CurrentCommand)
                da.Fill(dTables)
            End Using

            result = New List(Of String)

            For Each dr As DataRow In dTables.Rows
                result.Add(dr.Item(0).ToString.Trim)
            Next

        End Using

        Return result

    End Function

    Friend Function GetTable(ByVal TableName As String) As Table _
        Implements ISqlMethods.GetTable

        Dim result As Table

        Using dTables As New DataTable

            CurrentCommand.CommandText = "SHOW COLUMNS FROM `" & TableName & "`;"

            Using da As New MySqlDataAdapter(CurrentCommand)
                da.Fill(dTables)
            End Using

            result = New Table

            For Each dr As DataRow In dTables.Rows
                result.Fields.Add(Field.GetField(dr.Item(0).ToString.Trim, dr.Item(0).ToString.Trim))
            Next

        End Using

        Return result

    End Function


    Private disposedValue As Boolean = False        ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then

                If Not CurrentConnection Is Nothing Then
                    If CurrentConnection.State <> ConnectionState.Closed Then
                        Try
                            CurrentConnection.Close()
                        Catch ex As Exception
                        End Try
                    End If
                    CurrentConnection.Dispose()
                End If

                If Not CurrentCommand Is Nothing Then CurrentCommand.Dispose()

            End If
        End If
        Me.disposedValue = True
    End Sub

#Region " IDisposable Support "
    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class

<Serializable()> _
Public Class Database

    ' Some properties

    Private _TableList As BindingList(Of Table)

    Public ReadOnly Property TableList() As BindingList(Of Table)
        Get
            Return _TableList
        End Get
    End Property


    Public Sub New()

    End Sub

    Private Sub New(ByVal nSqlMethods As ISqlMethods)
        Fetch(nSqlMethods)
    End Sub


    Public Shared Function GetDatabase(ByVal nSqlMethods As ISqlMethods) As Database
        Return New Database(nSqlMethods)
    End Function


    Private Sub Fetch(ByVal nSqlMethods As ISqlMethods)

        _TableList = New BindingList(Of Table)
        For Each s As String In nSqlMethods.GetTableList
            _TableList.add(nSqlMethods.GetTable(s))
        Next

    End Sub


End Class

<Serializable()> _
Public Class Table

    Private _Fields As New BindingList(Of Field)

    Public ReadOnly Property Fields() As BindingList(Of Field)
        Get
            Return _Fields
        End Get
    End Property



End Class

<Serializable()> _
Public Class Field

    ' Some properties

    Public Shared Function GetField(ByVal nType As String, ByVal nName As String) As Field

    End Function

End Class

 

 

Coordinator
Oct 24, 2012 at 12:11 PM

hi, thanks for the examples and hints. I'll study and research it and try to implement the interfaces.

Coordinator
Oct 24, 2012 at 12:21 PM

Error out of haste:

Public Shared Function GetSqlMethods(ByVal ConnectionString As String)

Factory method can't implement interface, because it is supposed to be shared.

Developer
Nov 19, 2012 at 1:39 AM

if we make a class for each row and the data, would not that make the application eat more memory if for example the database to backup is too large?

Coordinator
Nov 19, 2012 at 8:07 AM
crayzyivan wrote:

if we make a class for each row and the data, would not that make the application eat more memory if for example the database to backup is too large?

 

In this case "Row class" is a behavior container not a data container. The purpose of "Row class" would be to encapsulate logic for fetching rows and writing data to a file. The class is not supposed to hold data in itself (at least for more then one row at a time).

Coordinator
Dec 14, 2012 at 5:28 AM

Hi Niemand,

I have just read the your message posted at [Source Code] > [Fork].

Regarding the 2 suggestion of:

1. Opening & Closing StreamWriter several times, and

2. SQL line in class Database method GetEvents: "SHOW EVENTS WHERE Db LIKE '" + DatabaseName + "';" might potentially cause errors due to case sensitivity.

are both implemented in version of 1.5.6.

However, about the 1st issue, the code is modified to Using the StreamWriter and open it once, then use StreamWriter.Flush() to write the text to files. Thus, this can avoid Opening & Closing the dump files several times.

About the project that you've uploaded in the [Issue Tracker], I'm still having a hard time to find and analyse the code. sorry about that ^^'

I'm thinking of using SQLite to store the Database Tables' structure, use it to compare to target database and performs the autoupgrade of table's structure.

and, the implementation of new class structure of the Routines(stored procedure, function, etc) will take some time to do it. Currently busy on something else. ^^

Thanks for the suggestions that provided, much appreciated. :)

Coordinator
Dec 19, 2012 at 2:46 PM
adriancs wrote:

I'm thinking of using SQLite to store the Database Tables' structure, use it to compare to target database and performs the autoupgrade of table's structure.

 

In my opinion plain xml is better in this case as it's easy reusable, parsable and editable without a specific connector and editor. The amount of data is also too small to justify database usage.

You can make structure classes serializable and then make use of built in xml serialization.

P.S. is there a stable  source or it's under constant development? I could rewrite the code to make it more abstract.

Coordinator
Dec 19, 2012 at 11:10 PM
Edited Dec 19, 2012 at 11:34 PM

Is this a stable source or its under constant development?

Some one asked me this question before when the code was in version 1.4.5. I answered him yes. But just for a few weeks later, new implementation applied.

Each time a version released, I feel that it should be a stable and constant.

But, as I can see now,
when new ideas come in,
when new experiences gathered,

things start evolving. Some of the significant functioning code stay remain/unchanged throughout several versions, some were extended, some were merged/moved, some were replaced and some were dropped. New version is always stand on top of current code.

So, don't worry, just write it.

You're welcome to rewrite the code and commit it directly to the source control. :)

I agree with you about the pros of using xml over SQLite. We'll start to develop it after this.