Auto-Generated visual basic source code from SQL

• Download MySQLMapping.zip - 2.6 MB

Introduction

Recently I have developing a domain name auction platform server program for my company. Part of the code required of database data updates, for example, we have a local WHOIS database for the verification of user submits domain auction order, and when the user submit his order through our email automatically summit server, then the domain goods in the order will be automatically verify through query http://who.is/whois database API, and then after user verification thought the security code which was sent to the domain’s whois email, the order will be generated and the server will updates both order database and whois database

Due to the reason of who.is/whois database have a lot of database fields in its whois_registry table, so that I have to writing a lot of property in a class to mapping a table between our server program and the MySQL database. So I decided to develop a small to automatically generate the source code of the database mapping operation to makes my coding job happy. :-):-):-):-):-):-)

 

How it works?

 

Parsing the create table definition

''' <summary>
''' Loading the table schema from a specific SQL doucment.
''' </summary>
''' <param name="Path"></param>
''' <returns></returns>
Public Function LoadSQLDoc(Path As String) As Reflection.Schema.Table()

The SQL schema dumping from the MySQL workbench data export tool is a well formatted document. Each table was define in the format show below, example as:

--
-- Table structure for table `server`
--

DROP TABLE IF EXISTS `server`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `server` (
  `guid` int(11) NOT NULL COMMENT 'configuration entry value',
  `value` longtext COMMENT 'the details server configuration data, please do not directly modify the configuration data at here, this will caused the seriously server internal error!',
  PRIMARY KEY (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='This table contains the server configuration data';
/*!40101 SET character_set_client = @saved_cs_client */;

Each definition was starts from a line: CREATE TABLE `tbl_name` and ends with a engine type and table comments, so that we could parsing each table definition statement using a regex expression

''' <summary>
''' Parsing the create table statement in the SQL document.
''' </summary>
Const SQL_CREATE_TABLE As String = "CREATE TABLE `.+?` \(.+?PRIMARY KEY \(`.+?`\).+?ENGINE=.+?;"

This expression works fine on the exports schema dumping.

When we have the CREATE TABLE definition statement, then we can start to parse the fields’ property in the statement:

In the schema dumping document, each filed definition was started with the field name and then follows the data type, then is the field property list, at last is the comment about the field.

`<field_name>` <data_type> [field_properties COMMENT 'comments',

The field definition can be described as above, the fields definition statement seems complexes, but fortunately, the field name and the field data type is not allowed space in the definition, so that we can easily get the two basically property of each  field in a table just using String.Split function. The comments about each field is also can be easily parsing using the regex expression:

''' <summary>
''' Regex expression for parsing the comments of the field in a table definition.
''' </summary>
Const FIELD_COMMENTS As String = "COMMENT '.+?',"

 

Creates the schema mapping

Now, before we creates the table schema, the data type between the MySQL and VisualBasic should be mapping. As the data type in each field definition is easily be parsed, so that the data type between the MySQL and VisualBasic is easily mapping between the SQL source code and VisualBasic source code:

#Region "Mapping the MySQL database type and visual basic data type"
     ''' <summary>
    ''' Mapping the MySQL database type and visual basic data type
    ''' </summary>
    ''' <param name="TypeDef"></param>
    ''' <returns></returns>
    Private Function InternalCreateDataType(TypeDef As String) As Reflection.DbAttributes.DataType

        Dim Type As Reflection.DbAttributes.MySqlDbType
        Dim Parameter As String = ""

        If Regex.Match(TypeDef, "int\(\d+\)").Success Then

            Type = Reflection.DbAttributes.MySqlDbType.Int64
            Parameter = InternalGetNumberValue(TypeDef)

        ElseIf Regex.Match(TypeDef, "varchar\(\d+\)").Success Then

            Type = Reflection.DbAttributes.MySqlDbType.VarChar
            Parameter = InternalGetNumberValue(TypeDef)

        ElseIf Regex.Match(TypeDef, "double").Success Then
            Type = Reflection.DbAttributes.MySqlDbType.Double

        ElseIf Regex.Match(TypeDef, "datetime").Success OrElse Regex.Match(TypeDef, "date").Success Then
            Type = Reflection.DbAttributes.MySqlDbType.DateTime

        ElseIf Regex.Match(TypeDef, "text").Success Then
            Type = Reflection.DbAttributes.MySqlDbType.Text

        Else

            'More complex type is not support yet, but you can easily extending the mapping code at here
            Throw New NotImplementedException($"Type define is not support yet for    {NameOf(TypeDef)}   >>> ""{TypeDef}""")

        End If

        Return New Reflection.DbAttributes.DataType(Type, Parameter)
    End Function

    Private Function InternalToDataType(TypeDef As Reflection.DbAttributes.DataType) As String

        Select Case TypeDef.MySQLType

            Case Reflection.DbAttributes.MySqlDbType.BigInt,
                 Reflection.DbAttributes.MySqlDbType.Int16,
                 Reflection.DbAttributes.MySqlDbType.Int24,
                 Reflection.DbAttributes.MySqlDbType.Int32,
                 Reflection.DbAttributes.MySqlDbType.MediumInt
                Return " As Integer"

            Case Reflection.DbAttributes.MySqlDbType.Bit,
                 Reflection.DbAttributes.MySqlDbType.Byte
                Return " As Byte"

            Case Reflection.DbAttributes.MySqlDbType.Date,
                 Reflection.DbAttributes.MySqlDbType.DateTime
                Return " As Date"

            Case Reflection.DbAttributes.MySqlDbType.Decimal
                Return " As Decimal"

            Case Reflection.DbAttributes.MySqlDbType.Double,
                 Reflection.DbAttributes.MySqlDbType.Float
                Return " As Double"

            Case Reflection.DbAttributes.MySqlDbType.Int64
                Return " As Long"

            Case Reflection.DbAttributes.MySqlDbType.UByte
                Return " As UByte"

            Case Reflection.DbAttributes.MySqlDbType.UInt16,
                 Reflection.DbAttributes.MySqlDbType.UInt24,
                 Reflection.DbAttributes.MySqlDbType.UInt32
                Return " As UInteger"

            Case Reflection.DbAttributes.MySqlDbType.UInt64
                Return " As ULong"

            Case Reflection.DbAttributes.MySqlDbType.LongText,
                 Reflection.DbAttributes.MySqlDbType.MediumText,
                 Reflection.DbAttributes.MySqlDbType.String,
                 Reflection.DbAttributes.MySqlDbType.Text,
                 Reflection.DbAttributes.MySqlDbType.TinyText,
                 Reflection.DbAttributes.MySqlDbType.VarChar,
                 Reflection.DbAttributes.MySqlDbType.VarString
                Return " As String"

            Case Else
                Throw New NotImplementedException($"{NameOf(TypeDef)}={TypeDef.ToString}")
        End Select
    End Function
#End Region

I just mapping several most usually used data type between the SQL source code and the VisualBasic source code, some other complex data type in MySQL is also can be mapping easily by extend the select statement in the two function which were show above.

Generates VisualBasic source code

Each table can be absolutely as a class in the visual basic, and I have defined a common structure for the mapping class in the VisualBasic:

Public MustInherit Class SQLTable

    Public MustOverride Function GetInsertSQL() As String
    Public MustOverride Function GetUpdateSQL() As String
    Public MustOverride Function GetDeleteSQL() As String

    Public Overrides Function ToString() As String
        Return GetInsertSQL()
    End Function

End Class

As the create table and select query is manual creates by developer in the database and his program, so that this code just generates the database updates methods in the MySQL: INSERT, UPDATE and DELETE.

Now we can start to create a class object in visualbasic from the function:

''' <summary>
''' Generate the class object definition to mapping a table in the mysql database.
''' </summary>
''' <param name="Table"></param>
''' <param name="DefSql"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function GenerateTableClass(Table As Reflection.Schema.Table, DefSql As String) As String

 

a. Generate the basically structure of a class object in VisualBasic

Reference to the visualbasic syntax about define a class:

''' <summary>
''' Comments
''' </summary>
Public Class <class_name> : Inherits BaseClass

   ''' <summary>
   ''' Comments
   ''' </summary>
   <CustomAttributes> Public Property Name As DataType

   ''' <summary>
   ''' Comments
   ''' </summary>
   Public Const Name As DataType = <InitValue>

   ''' <summary>
   ''' Comments
   ''' </summary>
   Public Function Name() As DataType
   End Function

End Class

So that we can easily generate the xml comments using the code:

Call CodeGenerator.AppendLine("''' <summary>")
Call CodeGenerator.AppendLine("''' " & Field.Comment)
Call CodeGenerator.AppendLine("''' </summary>")
Call CodeGenerator.AppendLine("''' <value></value>")

Generate the class header using the code:

Call CodeGenerator.AppendLine($"<Oracle.LinuxCompatibility.MySQL.Client.Reflection.DbAttributes.TableName(""{Table.TableName}"")>")
Call CodeGenerator.AppendLine($"Public Class {Table.TableName}: Inherits Oracle.LinuxCompatibility.MySQL.Client.SQLTable")

Generate the property using the code:

Call CodeGenerator.Append(InternalCreateAttribute(Field, IsPrimaryKey:=Table.PrimaryField.Contains(Field.FieldName))) 'Apply the custom attribute on the property
Call CodeGenerator.Append("Public Property " & Field.FieldName)      'Generate the property name
Call CodeGenerator.Append(InternalToDataType(Field.DataType))        'Generate the property data type

   

Private Function InternalCreateAttribute(Field As Reflection.Schema.Field, IsPrimaryKey As Boolean) As String
    
    Dim Code As String = $"    <DatabaseField(""{Field.FieldName}"")"

    If IsPrimaryKey Then
        Code &= ", PrimaryKey"
    End If

    If Field.AutoIncrement Then
        Code &= ", AutoIncrement"
    End If

    Code &= $", DataType({DataTypeFullNamesapce}.{Field.DataType.MySQLType.ToString}{If(String.IsNullOrEmpty(Field.DataType.ParameterValue), "", ", """ & Field.DataType.ParameterValue & """")})"
    Code &= "> "

    Return Code
End Function

 

Creates 3 updates method

Call CodeGenerator.AppendLine("#Region ""Public SQL Interface""")
Call CodeGenerator.AppendLine("#Region ""Interface SQL""")
Call CodeGenerator.AppendLine(Internal_INSERT_SQL(Table))
Call CodeGenerator.AppendLine(Internal_DELETE_SQL(Table))
Call CodeGenerator.AppendLine(Internal_UPDATE_SQL(Table))
Call CodeGenerator.AppendLine("#End Region")
Call CodeGenerator.AppendLine("    Public Overrides Function GetDeleteSQL() As String")
Call CodeGenerator.AppendLine(Internal_DELETE_SQL_Invoke(Table))
Call CodeGenerator.AppendLine("    End Function")
Call CodeGenerator.AppendLine("    Public Overrides Function GetInsertSQL() As String")
Call CodeGenerator.AppendLine(Internal_INSERT_SQL_Invoke(Table))
Call CodeGenerator.AppendLine("    End Function")
Call CodeGenerator.AppendLine("    Public Overrides Function GetUpdateSQL() As String")
Call CodeGenerator.AppendLine(Internal_UPDATE_SQL_Invoke(Table))
Call CodeGenerator.AppendLine("    End Function")
Call CodeGenerator.AppendLine("#End Region")

I want to makes the structure more clearly in the auto generated code, so that I separate the SQL definition and the function invoke:

The method invoke SQL was define as a shared read-only variable for a better performance, example as:

Private Function Internal_DELETE_SQL(Schema As Reflection.Schema.Table) As String
    Dim SqlBuilder As StringBuilder = New StringBuilder("    Private Shared ReadOnly DELETE_SQL As String = <SQL>%s</SQL>")
    Call SqlBuilder.Replace("%s", Reflection.SQL.SqlGenerateMethods.GenerateDeleteSql(Schema))

    Return SqlBuilder.ToString
End Function

How to create an INSERT, UPDATE and DELETE SQL from the MySQL table scheme, the method can be reviewed on one of my early codeproject article about mapping the class object between VisualBasic and MySQL database::-)

Quote:

Visual Basic Using Reflection to Map DataTable in MySQL Database

http://www.codeproject.com/Articles/638976/Visual-Basic-Using-Reflection-to-Map-DataTable-in

 

For invoke a SQL statement in the VisualBasic code, we just using the String.Format function to generate a completely SQL statement using the class object property, example as:

Private Function Internal_INSERT_SQL_Invoke(Schema As Reflection.Schema.Table) As String

    Dim SqlBuilder As StringBuilder = New StringBuilder("        ")
    Call SqlBuilder.Append("Return String.Format(INSERT_SQL, ")
    Call SqlBuilder.Append(String.Join(", ", (From Field In Schema.Fields Select InternalGetFieldValueInvoke(Field)).ToArray))
    Call SqlBuilder.Append(")")

    Return SqlBuilder.ToString
End Function

Using the code

Step 1. MySQL database schema dumping

We can easily create the MySQL database schema dumping using the MySQL workbench [Data Export] tool. When we have enter the data export interface, and make the target database selected, then we can settings up the dumping options:

Export to self-contained file and then setup the dump file path
Create dump in a single transaction
Skip table data:-)

 

Step 2. Convert SQL statement into VisualBasic source code

Convert the SQL file into the VisualBasic source code just needs simple 2 steps:

' Convert the SQL file into a visualbasic source code
Dim doc As String = Oracle.LinuxCompatibility.MySQL.Client.CodeGenerator.GenerateCode(SQL) 

Then when we have save the code into a *.vb source file, we have just finished a hard task work.

' Save the vb source code into a text file
doc.SaveTo(Output)

I have put this example source code usage into the reflector project in my uploaded file.

Imports Microsoft.VisualBasic.CommandLine.Reflection

Module CLIProgram

    Public Function Main() As Integer
        Return GetType(CLIProgram).RunCLI(arg:=Command)
    End Function

    <Command("--reflects", Info:="Automatically generates visualbasic source code from the MySQL database schema dump.",
                           Usage:="--reflects /sql <sql_path> [-o <output_path>]",
                           Example:="--reflects /sql ./test.sql")>
    <ParameterDescription("/sql", False, Description:="The file path of the MySQL database schema dump file."),
     ParameterDescription("-o", True, Description:="The output file path of the generated visual basic source code file from the SQL dump file ""/sql""")>
    Public Function Convert(argvs As Microsoft.VisualBasic.CommandLine.CommandLine) As Integer

        If Not argvs.CheckMissingRequiredParameters("/sql").IsNullOrEmpty Then
            Call Console.WriteLine("The required input parameter ""/sql"" is not specified!")
            Return -1
        End If

        Dim SQL As String = argvs("/sql"), Output As String = argvs("-o")

        If String.IsNullOrEmpty(Output) Then
            Output = FileIO.FileSystem.GetParentPath(SQL)
            Output = $"{Output}/{IO.Path.GetFileNameWithoutExtension(SQL)}.vb"
        End If

        If FileIO.FileSystem.FileExists(SQL) Then
<span style="font-size: 9pt;">            'Convert the SQL file into a visualbasic source code</span><span style="font-size: 9pt;">           
            Dim doc As String = Oracle.LinuxCompatibility.MySQL.Client.CodeGenerator.GenerateCode(SQL)
</span><span style="font-size: 9pt;">            Return CInt(doc.SaveTo(Output))      'Save the vb source code into a text file</span><span style="font-size: 9pt;">       
        Else</span><span style="font-size: 9pt;">           
            Call Console.WriteLine($"The target schema sql dump file ""{SQL}"" is not exists on your file system!")</span><span style="font-size: 9pt;">          
            Return -2</span><span style="font-size: 9pt;">       
        End If</span><span style="font-size: 9pt;">        </span>

        Return 0<span style="font-size: 9pt;">   
    End Function
</span><span style="font-size: 9pt;">End Module</span>

Example table in the SQL

--
-- Table structure for table `server`
--

DROP TABLE IF EXISTS `server`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `server` (
  `guid` int(11) NOT NULL COMMENT 'configuration entry value',
  `value` longtext COMMENT 'the details server configuration data, please do not directly modify the configuration data at here, this will caused the seriously server internal error!',
  PRIMARY KEY (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='This table contains the server configuration data';
/*!40101 SET character_set_client = @saved_cs_client */;

Example visualbasic source code output

Imports Oracle.LinuxCompatibility.MySQL.Client.Reflection.DbAttributes

''' <summary>
''' This table contains the server configuration data
'''
''' --
'''
''' DROP TABLE IF EXISTS `server`;
''' /*!40101 SET @saved_cs_client     = @@character_set_client */;
''' /*!40101 SET character_set_client = utf8 */;
''' CREATE TABLE `server` (
'''   `guid` int(11) NOT NULL COMMENT 'configuration entry value',
'''   `value` longtext COMMENT 'the details server configuration data, please do not directly modify the configuration data at here, this will caused the seriously server internal error!',
'''   PRIMARY KEY (`guid`)
''' ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='This table contains the server configuration data';
''' /*!40101 SET character_set_client = @saved_cs_client */;
'''
''' --
'''
''' </summary>
''' <remarks></remarks>
<Oracle.LinuxCompatibility.MySQL.Client.Reflection.DbAttributes.TableName("server")>
Public Class server: Inherits Oracle.LinuxCompatibility.MySQL.Client.SQLTable
#Region "Public Property Mapping To Database Fields"

''' <summary>
''' configuration entry value
''' </summary>
''' <value></value>
''' <returns></returns>
''' <remarks></remarks>
    <DatabaseField("guid"), PrimaryKey, DataType(MySqlDbType.Int64, "11")> Public Property guid As Long

''' <summary>
''' the details server configuration data, please do not directly modify the configuration data at here, this will caused the seriously server internal error!
''' </summary>
''' <value></value>
''' <returns></returns>
''' <remarks></remarks>
    <DatabaseField("value"), DataType(MySqlDbType.Text)> Public Property value As String

#End Region

#Region "Public SQL Interface"

#Region "Interface SQL"

    Private Shared ReadOnly INSERT_SQL As String = <SQL>INSERT INTO `server` (`guid`, `value`) VALUES ('{0}', '{1}');</SQL>
    Private Shared ReadOnly DELETE_SQL As String = <SQL>DELETE FROM `server` WHERE `guid`='{0}';</SQL>
    Private Shared ReadOnly UPDATE_SQL As String = <SQL>UPDATE `server` SET `guid`='{0}', `value`='{1}' WHERE `guid`='{2}';</SQL>

#End Region

    Public Overrides Function GetDeleteSQL() As String
        Return String.Format(DELETE_SQL, guid)
    End Function

    Public Overrides Function GetInsertSQL() As String
        Return String.Format(INSERT_SQL, guid, value)
    End Function

    Public Overrides Function GetUpdateSQL() As String
        Return String.Format(UPDATE_SQL, guid, value, guid)
    End Function

#End Region
End Class

The automatically generated source code can be easily convert to the c# code using SharpDevelop tool

Step 3. Compile your program

Now, things just be simple, open your server side program and adds the output source code file into your program or open the vs editor pasted the selected class object into your source code. No needs of the bridge coding between the MySQL database and your program now any more.

 

You see, this small utility tool save much of your time, this is another happy day! I hope you can enjoyed this code. :-)