65.9K
CodeProject 正在变化。 阅读更多。
Home

从 SQL 自动生成 Visual Basic 源代码

谢桂纲
starIconstarIconstarIconstarIconstarIcon

5.00/5 (4投票s)

2015 年 5 月 7 日

CPOL

5分钟阅读

viewsIcon

23360

downloadIcon

511

你看，这个小工具可以节省你大量的时间，又是一个快乐的日子！希望你能喜欢这段代码。

引言

最近我一直在为我的公司开发一个域名拍卖平台的服务器程序。其中一部分代码需要更新数据库数据，例如，我们有一个本地的 WHOIS 数据库用于验证用户提交的域名拍卖订单，当用户通过我们的电子邮件自动提交订单时，服务器将通过查询 http://who.is/whois 数据库 API 自动验证订单中的域名商品，然后在用户通过发送到域名的 WHOIS 电子邮件的安全码进行验证后，将生成订单，并且服务器将更新订单数据库和 WHOIS 数据库。

由于 who.is/whois 数据库的 whois_registry 表有很多数据库字段，所以我必须在类中编写很多属性来映射我们服务器程序和 MySQL 数据库之间的表。因此，我决定开发一个小型工具来自动生成数据库映射操作的源代码，让我的编码工作愉快。 :-):-):-):-):-):-)

 

它是如何工作的？

 

解析 CREATE TABLE 定义

''' <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()

从 MySQL Workbench 数据导出工具中转储的 SQL schema 是一个格式良好的文档。每个表都按照下面的格式定义，例如：

--
-- 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 */;

每个定义都以一行开头：CREATE TABLE `tbl_name`，并以引擎类型和表注释结尾，因此我们可以使用正则表达式解析每个表定义语句。

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

这个表达式在导出的 schema 转储中运行良好。

有了 CREATE TABLE 定义语句后，我们就可以开始解析语句中的字段属性了。

在 schema 转储文档中，每个字段定义都以字段名开头，然后是数据类型，接着是字段属性列表，最后是关于字段的注释。

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

字段定义如上所述，字段定义语句看起来很复杂，但幸运的是，字段名和字段数据类型在定义中不允许有空格，因此我们只需使用 String.Split 函数就可以轻松获取表中每个字段的两个基本属性。每个字段的注释也可以使用正则表达式轻松解析。

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

 

创建 schema 映射

现在，在我们创建表 schema 之前，MySQL 和 Visual Basic 之间的数据类型需要进行映射。由于每个字段定义中的数据类型很容易解析，因此 MySQL 和 Visual Basic 之间的数据类型在 SQL 源代码和 Visual Basic 源代码之间很容易进行映射。

#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

我只映射了 SQL 源代码和 Visual Basic 源代码之间几个最常用的数据类型，MySQL 中其他一些复杂的数据类型也可以通过扩展上面显示的两个函数中的 select 语句轻松进行映射。

生成 Visual Basic 源代码

每个表在 Visual Basic 中都可以绝对地作为一个类，我在 Visual Basic 中定义了一个通用的映射类结构。

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

由于 CREATE TABLE 和 SELECT 查询是由数据库和程序员手动创建的，因此此代码仅生成 MySQL 中的数据库更新方法：INSERT, UPDATE 和 DELETE。

现在我们可以开始从函数中创建一个 Visual Basic 中的类对象了。

''' <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. 在 Visual Basic 中生成类对象的基准结构

参考 Visual Basic 中定义类的语法。

''' <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

这样我们就可以轻松地使用代码生成 XML 注释了。

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

使用代码生成类头。

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")

使用代码生成属性。

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

 

创建 3 个更新方法。

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")

我想让自动生成代码中的结构更清晰，所以将 SQL 定义和函数调用分开了。

SQL 方法调用定义为共享只读变量，以获得更好的性能，例如：

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

如何从 MySQL 表 schema 创建 INSERT, UPDATE 和 DELETE SQL，方法可以在我早期的一篇 CodeProject 文章中找到，该文章关于在 Visual Basic 和 MySQL 数据库之间映射类对象：::-)

引用
Visual Basic 使用反射映射 MySQL 数据库中的 DataTable
https://codeproject.org.cn/Articles/638976/Visual-Basic-Using-Reflection-to-Map-DataTable-in

 

要调用 Visual Basic 代码中的 SQL 语句，我们只需使用 String.Format 函数，使用类对象属性生成完整的 SQL 语句，例如：

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

使用代码

第一步。MySQL 数据库 schema 转储

我们可以使用 MySQL Workbench 的 [Data Export] 工具轻松创建 MySQL 数据库 schema 转储。当我们进入数据导出界面并选择目标数据库后，就可以设置转储选项了。

导出到自包含文件，然后设置转储文件路径。
在一个事务中创建转储。
跳过表数据。:-)

 

第二步。将 SQL 语句转换为 Visual Basic 源代码

将 SQL 文件转换为 Visual Basic 源代码只需简单的 2 个步骤。

转换 SQL 文件并获取 Visual Basic 代码。
' Convert the SQL file into a visualbasic source code
Dim doc As String = Oracle.LinuxCompatibility.MySQL.Client.CodeGenerator.GenerateCode(SQL)

然后，当我们把代码保存到一个 *.vb 源文件中，我们就已经完成了艰巨的工作。

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

我已经将这个示例源代码的使用放在了我上传文件中的 Reflector 项目里。

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
            'Convert the SQL file into a visualbasic source code           
            Dim doc As String = Oracle.LinuxCompatibility.MySQL.Client.CodeGenerator.GenerateCode(SQL)
            Return CInt(doc.SaveTo(Output))      'Save the vb source code into a text file       
        Else           
            Call Console.WriteLine($"The target schema sql dump file ""{SQL}"" is not exists on your file system!")          
            Return -2       
        End If        

        Return 0   
    End Function
End Module

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 */;

Visual Basic 源代码输出示例

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

自动生成的源代码可以使用 SharpDevelop 工具轻松转换为 C# 代码。

第三步。编译你的程序

现在，事情变得很简单了，打开你的服务器端程序，并将输出的源代码文件添加到你的程序中，或者打开 VS 编辑器，将选定的类对象粘贴到你的源代码中。现在不再需要 MySQL 数据库和你的程序之间的桥接编码了。

 

你看，这个小工具可以节省你大量的时间，又是一个快乐的日子！希望你能喜欢这段代码。 :-)

© . All rights reserved.