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
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:
DROP TABLE IF EXISTS `server`;
;
;
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';
;
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
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"
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
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:
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:
Public Class <class_name> : Inherits BaseClass
<CustomAttributes> Public Property Name As DataType
Public Const Name As DataType = <InitValue>
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)))
Call CodeGenerator.Append("Public Property " & Field.FieldName)
Call CodeGenerator.Append(InternalToDataType(Field.DataType))
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 and gets the VisualBasic 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.
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;">
Dim doc As String = Oracle.LinuxCompatibility.MySQL.Client.CodeGenerator.GenerateCode(SQL)
</span><span style="font-size: 9pt;"> Return CInt(doc.SaveTo(Output))
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
`value` longtext COMMENT
PRIMARY KEY (`guid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=
/*!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 */;
''' ;
''' 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';
''' ;
'''
''' --
'''
''' </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. :-)